Cohort Retention Analysis

WITH cohorts AS (
    SELECT 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_type = 'login'
    GROUP  BY user_id, date_trunc('week', event_at)
),
joined AS (
    SELECT c.cohort_week,
           a.active_week,
           ((a.active_week - c.cohort_week) / 7)::int AS week_offset
    FROM   cohorts c
    LEFT   JOIN activity a ON a.user_id = c.id
    WHERE  a.active_week IS NULL OR a.active_week >= c.cohort_week
)
SELECT cohort_week,
       COUNT(DISTINCT CASE WHEN week_offset = 0 THEN cohort_week END) AS w0_size,
       COUNT(DISTINCT CASE WHEN week_offset = 1 THEN cohort_week END) AS w1,
       COUNT(DISTINCT CASE WHEN week_offset = 2 THEN cohort_week END) AS w2,
       COUNT(DISTINCT CASE WHEN week_offset = 4 THEN cohort_week END) AS w4,
       COUNT(DISTINCT CASE WHEN week_offset = 8 THEN cohort_week END) AS w8
FROM   joined
GROUP  BY cohort_week
ORDER  BY cohort_week;