postgresql monthly usage retention query

Question

I'm trying to write a query that checks customer retention.

This is my query:

with users_per_month as (
    select count(distinct user_id)         as count_active_users,
           array_agg(distinct user_id)     as active_users,
           date_trunc('month', user_id) as month
    from app_logs
    group by month
),
     users_per_4_month as (
         select month,
                active_users,
                lead(active_users) over (order by month)    as lead_1,
                lead(active_users, 2) over (order by month) as lead_2,
                lead(active_users, 3) over (order by month) as lead_3,
                lead(active_users, 4) over (order by month) as lead_4
         from users_per_month
     )

select *
from users_per_4_month;

But I get users in a subsequent month who were not in the first month. like user_id 3 in March (lead_3) this is the result:

any help will be helpful, tnx :)

Answer 1

It works :)

with per_month as (
    select to_char(a1.device_timestamp, 'yyyy-mm') as month,
           array_agg(distinct a1.user_id)    as active_users
    from facetune2_usage_app_background a1
    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
     ),
     m3 as (
         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
         from m2
     )
select month,
       num_users,
       '100' as "0",
       january_retained * 100 / num_users as "1",
       febrary_retained * 100 / num_users as "2",
       march_retained * 100 / num_users as "3",
       april_retained * 100 / num_users as "4"
from m3