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
