From raw events to board-ready dashboards.

-- 7-day retention by signup cohort
with cohorts as (
  select user_id,
         date_trunc('week', created_at) as cohort_week
  from users
),
activity as (
  select user_id,
         date_trunc('week', event_at) as active_week
  from events
  where event_name = 'session_start'
  group by 1, 2
)
select c.cohort_week,
       count(distinct c.user_id)                      as signups,
       count(distinct a.user_id)                      as retained,
       round(count(distinct a.user_id)
           / count(distinct c.user_id)::numeric, 3)   as retention
from cohorts c
left join activity a
  on a.user_id = c.user_id
 and a.active_week = c.cohort_week + interval '7 days'
group by c.cohort_week
order by c.cohort_week;