sql
WITH users AS (
SELECT user_id, date_trunc('week', signup_date)::date AS cohort_week
FROM user_table
WHERE signup_date IS NOT NULL
),
activity_weeks AS (
SELECT
a.user_id,
date_trunc('week', a.activity_date)::date AS activity_week
FROM activity_table a
WHERE a.activity_date IS NOT NULL
),
user_activity AS (
-- join to get cohort and activity week, compute offset
SELECT
u.cohort_week,
ua.user_id,
ua.activity_week,
EXTRACT(epoch FROM (ua.activity_week - u.cohort_week)) / 604800 AS week_offset
FROM users u
LEFT JOIN activity_weeks ua
ON ua.user_id = u.user_id
),
filtered_offsets AS (
-- keep offsets 0..11 and dedupe user-week pairs
SELECT cohort_week, user_id, CAST(week_offset AS int) AS week_offset
FROM user_activity
WHERE week_offset BETWEEN 0 AND 11
GROUP BY cohort_week, user_id, CAST(week_offset AS int)
),
cohort_sizes AS (
SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_users
FROM users
GROUP BY cohort_week
),
retention_counts AS (
-- count distinct users active per cohort/offset
SELECT cohort_week, week_offset, COUNT(DISTINCT user_id) AS active_users
FROM filtered_offsets
GROUP BY cohort_week, week_offset
),
-- ensure every cohort has rows for offsets 0..11 (missing => 0)
all_offsets AS (
SELECT cohort_week, offs AS week_offset
FROM (SELECT DISTINCT cohort_week FROM users) cw
CROSS JOIN generate_series(0,11) AS offs
),
retention_full AS (
SELECT
a.cohort_week,
a.week_offset,
COALESCE(r.active_users, 0) AS active_users,
c.cohort_users,
CASE WHEN c.cohort_users = 0 THEN 0
ELSE ROUND(100.0 * COALESCE(r.active_users,0) / c.cohort_users, 2)
END AS retention_pct
FROM all_offsets a
LEFT JOIN retention_counts r
ON r.cohort_week = a.cohort_week AND r.week_offset = a.week_offset
LEFT JOIN cohort_sizes c
ON c.cohort_week = a.cohort_week
)
-- pivot into matrix: cohort_week rows, week_offset columns
SELECT cohort_week,
MAX(CASE WHEN week_offset = 0 THEN retention_pct END) AS w0,
MAX(CASE WHEN week_offset = 1 THEN retention_pct END) AS w1,
MAX(CASE WHEN week_offset = 2 THEN retention_pct END) AS w2,
MAX(CASE WHEN week_offset = 3 THEN retention_pct END) AS w3,
MAX(CASE WHEN week_offset = 4 THEN retention_pct END) AS w4,
MAX(CASE WHEN week_offset = 5 THEN retention_pct END) AS w5,
MAX(CASE WHEN week_offset = 6 THEN retention_pct END) AS w6,
MAX(CASE WHEN week_offset = 7 THEN retention_pct END) AS w7,
MAX(CASE WHEN week_offset = 8 THEN retention_pct END) AS w8,
MAX(CASE WHEN week_offset = 9 THEN retention_pct END) AS w9,
MAX(CASE WHEN week_offset = 10 THEN retention_pct END) AS w10,
MAX(CASE WHEN week_offset = 11 THEN retention_pct END) AS w11
FROM retention_full
GROUP BY cohort_week
ORDER BY cohort_week DESC;
