user activity, PostgreSQL

Language: Layout:

drop table if exists user_activity cascade;

create table user_activity (user_id int, created_at timestamp);
insert into user_activity
select (random() * 249 + 1)::int, 
       timestamp '2018-12-01' + make_interval(days => (random() * 300 + 1)::int)
from generate_series(1,1000)
;

with per_month as (
  select to_char(a1.created_at, 'yyyy-mm') as month,
         array_agg(distinct a1.user_id) as active_users
  from user_activity a1
  where a1.created_at >= timestamp '2018-12-01'
  group by month
), m2 as (
  select month, 
         active_users,
         lead(active_users) over (order by month) as january_users,
         lead(active_users,2) over (order by month) as february_users,
        lead(active_users,3) over (order by month) as march_users,
        lead(active_users,4) over (order by month) as april_users,
        lead(active_users,5) over (order by month) as may_users,
        lead(active_users,6) over (order by month) as june_users,
        lead(active_users,7) over (order by month) as july_users,
        lead(active_users,8) over (order by month) as august_users
  from per_month 
)
select month,
       cardinality(active_users) as num_users,
       (select count(*) from (select unnest(active_users) intersect select unnest(january_users)) t)  as january_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(february_users)) t)  as febrary_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(march_users)) t)  as march_retained,       
       (select count(*) from (select unnest(active_users) intersect select unnest(april_users)) t)  as april_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(may_users)) t)  as may_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(june_users)) t)  as june_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(july_users)) t)  as july_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(august_users)) t)  as august_retained
from m2;


absolute service time: 0,58 sec