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
  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
from m2;


absolute service time: 0,55 sec