SQL Query examples

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

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

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

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

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

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