Build a weekly cohort retention table in SQL from a signups and an activity table
You have signups(user_id, signup_week) and activity(user_id, active_week) — one row per user per week they were active. Produce a cohort retention table: for each signup_week cohort, count how many users were active weeks_since_signup = 0, 1, 2, … after signup, where "retained in week k" means the user was active in the week k weeks after their signup.
Constraints: rows are the signup cohort, the measure is weeks-since-signup, and each cohort's week-0 count is the denominator for its retention rate. Do not double-count a user within a cell.
-- signup_week, weeks_since_signup, retained_users
-- (retention rate = retained_users / cohort week-0 size)Write the query.
Join activity to signups on user_id, derive weeks_since_signup = active_week - signup_week, then GROUP BY signup_week, weeks_since_signup with COUNT(DISTINCT user_id). Each cohort's week-0 count is the denominator for its retention rate.
- ✗Grouping by calendar week instead of weeks-since-signup
- ✗Using total signups as the denominator instead of each cohort's week 0
- ✗Using COUNT(*) instead of COUNT(DISTINCT user_id) and double-counting
- →How would you switch this from weekly to N-day cohorts?
- →How would you fill missing weeks so gaps show as 0, not absent rows?
Join activity to signups, compute the week offset, and group by cohort and weeks-since-signup, taking COUNT(DISTINCT user_id):
WITH cohort AS (
SELECT s.user_id,
s.signup_week,
a.active_week - s.signup_week AS weeks_since_signup
FROM signups s
JOIN activity a ON a.user_id = s.user_id
WHERE a.active_week >= s.signup_week
)
SELECT signup_week,
weeks_since_signup,
COUNT(DISTINCT user_id) AS retained_users,
ROUND(
COUNT(DISTINCT user_id) * 1.0
/ MAX(COUNT(DISTINCT user_id)) OVER (PARTITION BY signup_week),
3
) AS retention_rate
FROM cohort
GROUP BY signup_week, weeks_since_signup
ORDER BY signup_week, weeks_since_signup;weeks_since_signup = 0 is the cohort size; the windowed MAX(...) OVER (PARTITION BY signup_week) picks it as the denominator, so week 0 is always 1.0. COUNT(DISTINCT user_id) prevents double-counting a user, and the active_week >= signup_week filter drops any pre-signup activity.
