Approach: compute each user’s cohort_week (start of week containing their signup), then for each cohort and each week_offset (0..3) count how many users from that cohort had any event in the target week. Divide by cohort size to get retention_rate.SQL (ANSI/Postgres dialect shown; uses date_trunc and FILTER as requested):sql
WITH user_cohorts AS (
SELECT
user_id,
date_trunc('week', MIN(event_date) FILTER (WHERE event_name = 'signup'))::date AS cohort_week
FROM events
GROUP BY user_id
),
cohort_sizes AS (
SELECT cohort_week, COUNT(*) AS cohort_users
FROM user_cohorts
GROUP BY cohort_week
),
week_offsets AS (
-- produce offsets 0..3
SELECT 0 AS week_offset UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
),
cohort_activity AS (
SELECT
uc.cohort_week,
wo.week_offset,
COUNT(DISTINCT uc.user_id) AS active_users
FROM user_cohorts uc
CROSS JOIN week_offsets wo
LEFT JOIN events e
ON e.user_id = uc.user_id
AND e.event_date >= (uc.cohort_week + (wo.week_offset * INTERVAL '1 week'))::date
AND e.event_date < (uc.cohort_week + ((wo.week_offset + 1) * INTERVAL '1 week'))::date
GROUP BY uc.cohort_week, wo.week_offset
)
SELECT
ca.cohort_week,
ca.week_offset,
COALESCE(ca.active_users, 0) AS active_users,
cs.cohort_users,
ROUND((COALESCE(ca.active_users,0)::numeric / cs.cohort_users) * 100, 2) AS retention_rate_pct
FROM cohort_activity ca
JOIN cohort_sizes cs USING (cohort_week)
ORDER BY cohort_week, week_offset;
