Cohort retention triangle — signup-month cohorts by months since signup
Tables users(user_id, signup_date) and activity(user_id, activity_date). Build a cohort retention triangle — for each signup-month cohort and each whole month since signup, the percent of the cohort active that month. Month 0 is the signup month.
-- cohort_month, months_since, retention_pct (share of the cohort active)Write the query.
Bucket each signup into its cohort month and size the cohort with COUNT(*). Compute months_since from the year-and-month gap between activity month and cohort month, then divide distinct actives per (cohort, months_since) by that fixed cohort size — not by a moving base.
- ✗Using the previous month's actives as a moving denominator
- ✗Approximating months elapsed as day-difference over 30
- ✗Sizing the cohort from month-0 actives instead of all signups
- →Why is a fixed cohort-size denominator better than a moving one?
- →How would you left-fill months where a cohort had zero actives?
Bucket each user into their signup month, measure the cohort size once, then for every active month express distinct actives as a percent of that fixed size:
WITH cohort AS (
SELECT user_id, date_trunc('month', signup_date)::date AS cohort_month
FROM users
),
sizes AS (
SELECT cohort_month, COUNT(*) AS cohort_size
FROM cohort
GROUP BY cohort_month
),
active AS (
SELECT c.cohort_month,
12 * (EXTRACT(YEAR FROM a.activity_date) - EXTRACT(YEAR FROM c.cohort_month))
+ (EXTRACT(MONTH FROM a.activity_date) - EXTRACT(MONTH FROM c.cohort_month)) AS months_since,
a.user_id
FROM cohort c
JOIN activity a ON a.user_id = c.user_id
)
SELECT a.cohort_month,
a.months_since,
ROUND(100.0 * COUNT(DISTINCT a.user_id) / s.cohort_size, 2) AS retention_pct
FROM active a
JOIN sizes s ON s.cohort_month = a.cohort_month
GROUP BY a.cohort_month, a.months_since, s.cohort_size
ORDER BY a.cohort_month, a.months_since;The denominator is the cohort's total signups, held constant across all months_since — that is what makes the triangle comparable (month 0 = 100%, later months decay). months_since is the whole-month difference, not days / 30, which would drift. Sizing the cohort from month-0 actives would silently drop users who signed up but were not active that month.
