30 day retention in SQL

retention.sql

with cohorts as (
  select id as user_id, date_trunc('day', created_at) as started_at from users group by 1, 2
)

,logins as (
    select 
        distinct on (user_id, time_between)
        user_id, 
        started_at, 
        checkin.happened_at, 
        FLOOR(EXTRACT(epoch from (happened_at - cohorts.started_at)) / (60 * 60 * 24)) as time_between
    from cohorts inner join checkin using (user_id) order by user_id, time_between
)


select 
  started_at as cohort,
  count(distinct user_id) as size,
  count(distinct case when time_between>=1 then user_id end) / count(distinct user_id)::numeric as t1,
  count(distinct case when time_between>=2 then user_id end) / count(distinct user_id)::numeric as t2,
  count(distinct case when time_between>=3 then user_id end) / count(distinct user_id)::numeric as t3,
  count(distinct case when time_between>=4 then user_id end) / count(distinct user_id)::numeric as t4,
  count(distinct case when time_between>=5 then user_id end) / count(distinct user_id)::numeric as t5,
  count(distinct case when time_between>=6 then user_id end) / count(distinct user_id)::numeric as t6,
  count(distinct case when time_between>=7 then user_id end) / count(distinct user_id)::numeric as t7,
  count(distinct case when time_between>=8 then user_id end) / count(distinct user_id)::numeric as t8,
  count(distinct case when time_between>=9 then user_id end) / count(distinct user_id)::numeric as t9,
  count(distinct case when time_between>=10 then user_id end) / count(distinct user_id)::numeric as t10,
  
  count(distinct case when time_between>=11 then user_id end) / count(distinct user_id)::numeric as t11,
  count(distinct case when time_between>=12 then user_id end) / count(distinct user_id)::numeric as t12,
  count(distinct case when time_between>=13 then user_id end) / count(distinct user_id)::numeric as t13,
  count(distinct case when time_between>=14 then user_id end) / count(distinct user_id)::numeric as t14,
  count(distinct case when time_between>=15 then user_id end) / count(distinct user_id)::numeric as t15,
  count(distinct case when time_between>=16 then user_id end) / count(distinct user_id)::numeric as t16,
  count(distinct case when time_between>=17 then user_id end) / count(distinct user_id)::numeric as t17,
  count(distinct case when time_between>=18 then user_id end) / count(distinct user_id)::numeric as t18,
  count(distinct case when time_between>=19 then user_id end) / count(distinct user_id)::numeric as t19,
  count(distinct case when time_between>=20 then user_id end) / count(distinct user_id)::numeric as t20,
  
  count(distinct case when time_between>=21 then user_id end) / count(distinct user_id)::numeric as t21,
  count(distinct case when time_between>=22 then user_id end) / count(distinct user_id)::numeric as t22,
  count(distinct case when time_between>=23 then user_id end) / count(distinct user_id)::numeric as t23,
  count(distinct case when time_between>=24 then user_id end) / count(distinct user_id)::numeric as t24,
  count(distinct case when time_between>=25 then user_id end) / count(distinct user_id)::numeric as t25,
  count(distinct case when time_between>=26 then user_id end) / count(distinct user_id)::numeric as t26,
  count(distinct case when time_between>=27 then user_id end) / count(distinct user_id)::numeric as t27,
  count(distinct case when time_between>=28 then user_id end) / count(distinct user_id)::numeric as t28,
  count(distinct case when time_between>=29 then user_id end) / count(distinct user_id)::numeric as t29,
  count(distinct case when time_between>=30 then user_id end) / count(distinct user_id)::numeric as t30
  
from logins group by 1 order by cohort desc;