user activity 4, 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
  v_flat_actives(event_year, event_month, user_id) AS (
    SELECT DISTINCT
        EXTRACT(YEAR FROM created_at)::INT AS event_year,
        EXTRACT(MONTH FROM created_at)::INT AS event_month,
        user_id
    FROM user_activity
    ORDER BY event_year, event_month
  ),
  v_active_months(event_year, event_month) AS (
    SELECT DISTINCT
        event_year::INT,
        event_month::INT
    FROM v_flat_actives 
    ORDER BY event_year, event_month
  ),
  r_actives AS (
    WITH RECURSIVE
      i_actives(s_year, s_month, c_year, c_month, e_year, e_month, the_end, user_ids, actives) AS (
          SELECT
            vam.event_year AS s_year,
            vam.event_month AS s_month,
            vam.event_year AS c_year,
            vam.event_month AS c_month,
            EXTRACT(YEAR FROM MAKE_DATE(vam.event_year, vam.event_month, 1) + interval '3 month')::INT AS e_year,
            EXTRACT(MONTH FROM MAKE_DATE(vam.event_year, vam.event_month, 1) + interval '3 month')::INT AS e_month,
            FALSE AS the_end,
            ARRAY(
                SELECT vf.user_id 
                FROM v_flat_actives vf
                WHERE vf.event_year = vam.event_year
                AND vf.event_month = vam.event_month
            ) AS user_ids,
            ARRAY[]::BIGINT[] AS actives
          FROM v_active_months vam
        UNION
          SELECT
            ia.s_year AS s_year,
            ia.s_month AS s_month,
            EXTRACT(YEAR FROM MAKE_DATE(ia.c_year, ia.c_month, 1) + interval '1 month')::INT AS c_year,
            EXTRACT(MONTH FROM MAKE_DATE(ia.c_year, ia.c_month, 1) + interval '1 month')::INT AS c_month,
            ia.e_year AS e_year,
            ia.e_month AS e_month,
            (ia.c_year > ia.e_year OR (ia.c_year = ia.e_year AND ia.c_month >= ia.e_month)) AS the_end,
            ia.user_ids,
            ia.actives || (
                SELECT COUNT(*)
                FROM v_flat_actives fa
                WHERE fa.event_year = ia.c_year
                AND fa.event_month = ia.c_month
                AND fa.user_id = ANY (ia.user_ids)
            )
          FROM i_actives ia
          WHERE ia.c_year < ia.e_year OR (ia.c_year = ia.e_year AND ia.c_month <= ia.e_month)
      )
    SELECT s_year, s_month, c_year, c_month, e_year, e_month, the_end, actives
    FROM i_actives
   )
SELECT s_year, s_month, array_to_string(actives, ', ')
FROM r_actives
WHERE the_end = TRUE
ORDER BY s_year, s_month;

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,52 sec