Untitled

 avatar
unknown
plain_text
3 months ago
742 B
8
Indexable
with month_year as ( 
  select DISTINCT 
    user_id, 
    extract(YEAR from event_date) as year, 
    extract(MONTH from event_date) as month 
  from user_actions 
  where event_type is not null
),
active as ( 
  select 
    curr.user_id, 
    curr.year as curr_year, 
    curr.month as curr_month, 
    prev.year as prev_year, 
    prev.month as prev_month
  
  from 
    month_year as curr 
    left join 
    month_year as prev 
    on 
      curr.user_id = prev.user_id 
      and curr.year = prev.year 
      and curr.month - prev.month =  +1
  order by 1,2,3    
)
select distinct curr_month,
  count(prev_month)
        
from active 
where prev_month is not null
GROUP by 1

-- active is defined as = active in month m and month m-1 
Editor is loading...
Leave a Comment