Untitled
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