SQL tips

Conditions with dates

1. To set the difference between two dates, it is better to use the following expression: eventtime - created <= interval ‘10 min’ (instead of eventtime - created <= 600000)

2. To set a relative date, instead of created <= to_timestamp('2020-11-01 12:59:59', 'YYYY-MM-DD HH24:MI:SS') it is better to use created < date ‘2020-11-02’ or created <= timestamp ‘2020-11-01 12:59:59’ if you need to add the exact time.

3. To include a specific date to the query result (for example 30th of November 2020) you can apply the following condition: created < date '2020-12-01' or created <= timestamp '2020-11-30 23:59:59' The first option is preferable.

Countries

The country names are stored in two-letter code form, such as ‘CN’, ‘FR’, ‘GB’, etc.Therefore, instead of the full country name (‘China’) you can use a code ('CN') or use the countries dictionary - and p.country <> ANY(select code from countries where name = 'China')

DAU (Daily active users)

Active user is a user who generates any event (basic, custom, Start session event) via SDK or API over a certain period of time, for example, a day for DAU. You can use a more convenient table with users’ activity to calculate DAU: users get added only once per day if they are active.

select eventdate, count(devtodevid)
from p103198.dau
where eventdate >= current_date - interval '1 month'
group by 1
order by 1 asc

It corresponds to the DAU metric in the Basic Metrics report. Users marked as cheater or tester are not included in this table.

User segments:

To see the segments the user belongs to:

SELECT  devtodevid, segmentvalues
FROM p102968.users
WHERE segmentvalues is not null 

To filter users and select those who belong to the 'test_segment' segment:

SELECT  devtodevid
FROM p102968.users
WHERE segmentvalues->'test_segment' is true 
or segmentvalues->'other_segment' is not true

To filter users and select those who belong to the specific A/B test group:

select devtodevid, abtestvalues
from p105424.users u
where u.lasttime >= current_date - interval '1 year' 
and u.abtestvalues->'test_name.A' is not null

Currency conversion

When we receive payment information, we convert it into United States Dollar (USD) using the latest exchange rate. Thus, devtodev stores information about payment amounts in USD.

However, if you want to calculate your financial metrics in Euro (EUR), Japanese Yen (JPY), or any other currency, you can use the currencies_historical table which contains exchange rates of multiple currencies to the U.S. dollar for each day.

select 
  p.date,
  p.usd,
  round(p.usd/eur.rate::numeric, 2) as eur
from (select  
        eventtime::date as date,
        sum(p.priceusd) as usd
      from p102968.payments p
      where p.eventtime >= current_date - interval '30 day' and p.eventtime < current_date
      group by 1) p
  left join public.currencies_historical eur on 
      eur.source = 'EUR' 
      and eur.update_date = p.date
order by p.date