SQL Query examples

Example 1

The result of this query is a list of user events for the last 2 days sorted by the amount of times the event was performed.

with last_ev as 
    (select distinct devtodevid, max(eventtime) as eventtime
    from p104704.custom_events 
    where created >= current_date - interval '2 days'
    group by 1)
select name, count(distinct devtodevid)
from 
  (select distinct q1.devtodevid, name
  from p104704.custom_events q1 
    join last_ev q2 using (devtodevid, eventtime)) q3
group by 1
order by 2 desc

Example 2

The result of this query is a list of last user events before they churn. The churn period is specified.

with last_ev as
    (select distinct devtodevid, max(eventtime) as eventtime
    from p104704.custom_events ev 
    join p104704.users us using(devtodevid)
    where us.lasttime <= current_date - interval '7 days'  -- churn period
    and ev.created >= current_date - interval '30 days'
    group by 1)
select name, count(distinct devtodevid)
from
  (select distinct q1.devtodevid, name
  from p104704.custom_events q1
    join last_ev q2 using (devtodevid, eventtime)) q3
group by 1
order by 2 desc

Example 3

The result of this query is a timestamp of the last event with numeric value.

select tm(eventtime) as "event timestamp", data
from p104704.user_property_updates 
where data::text SIMILAR TO '%customUserProperyName":\d%' 
order by eventtime desc 
limit 1

The result of this query is a timestamp of the last event with text (string) value.

select tm(eventtime) as "event timestamp", data
from p104704.user_property_updates
where data::text SIMILAR TO '%customUserProperyName":"%' 
order by eventtime desc 
limit 1

Example 4

Use this query to find the users with decreasing player level (events with decreasing player level alert). The result of this query is a list of users and their previous and current levels.

select devtodevid, prev_event_lvl, cur_event_lvl
from(
  select devtodevid
  , lag(eventlevel,1) over (partition by devtodevid order by eventtime) as prev_event_lvl
  , eventlevel as cur_event_lvl
  from p104704.levelups
  where created >= current_date - interval '7 day' and eventtime < current_date
) t
where prev_event_lvl > cur_event_lvl
order by devtodevid

Example 5

Use this query to calculate user retention. The result of this query is a table that contains the number of new users per day and the number of returning users on the 0th, 1st, 2nd, 3rd, 7th, and 14th day after installation.

with return as (
  select created::date as date_, 
    count(devtodevid) filter (where retentionday = 0) as R0,
    count(devtodevid) filter (where retentionday = 1) as R1,
    count(devtodevid) filter (where retentionday = 2) as R2,
    count(devtodevid) filter (where retentionday = 3) as R3,
    count(devtodevid) filter (where retentionday = 7) as R7,
    count(devtodevid) filter (where retentionday = 14) as R14
  from p102968.returns_by_24h 
  where created::date >= current_date - interval '1 month'
  and eventtime >= current_date - interval '1 month'
  group by 1
  )
, us as (
  select created::date as date_, count(1) AS regs
  from p102968.users
  where created::date >= current_date - interval '1 month'
  group by 1
  )
select us.date_, regs, r0, r1, r2, r3, r7, r14
from us 
  left join return r on us.date_=r.date_
order by 1

Example 6

See revenue for a year by monthly cohorts. The result table will show revenue by each cohort for each month.

You can modify the periods for the cohorts and payment events.

select to_char(created, 'yyyy-mm') as month 
, count(distinct devtodevid) as users_in_cohort 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-01-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-02-01', 'yyyy-mm-dd')) as jan_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-02-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-03-01', 'yyyy-mm-dd')) as feb_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-03-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-04-01', 'yyyy-mm-dd')) as mar_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-04-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-05-01', 'yyyy-mm-dd')) as apr_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-05-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-06-01', 'yyyy-mm-dd')) as may_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-06-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-07-01', 'yyyy-mm-dd')) as june_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-07-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-08-01', 'yyyy-mm-dd')) as july_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-08-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-09-01', 'yyyy-mm-dd')) as aug_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-09-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-10-01', 'yyyy-mm-dd')) as sep_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-10-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-11-01', 'yyyy-mm-dd')) as oct_rev 
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-11-01', 'yyyy-mm-dd') and p.eventtime < to_date('2024-12-01', 'yyyy-mm-dd')) as nov_rev
, sum(revenueusd) filter (where p.eventtime >= to_date('2024-12-01', 'yyyy-mm-dd') and p.eventtime < to_date('2025-01-01', 'yyyy-mm-dd')) as dec_rev
from p104704.payments p
where created >= to_date('2024-01-01', 'yyyy-mm-dd') and created < to_date('2025-01-01', 'yyyy-mm-dd') -- new users during this period
and tester is false 
and cheater is false
group by 1
order by 1 asc