Cohort retention in SQL: the triangle table and the tail that goes up
Signup month by months since signup, the query that builds the table, why month 0 must be 100%, the triangle you should expect, and why the blended retention curve bends up when only the oldest cohorts are left: one page.
Get the free PDF
One page, print-ready, free to share. No signup needed.
Quarterly review, slide 14: month 10 retention went up. Nobody came back. The curve is an average over whoever has reached month 10, and by then that is three cohorts of early adopters. This page covers the cohort table (signup month by months since signup), the query that builds it, the month 0 check, the triangle, and why the blended tail bends up. The print-ready A4 PDF is at the bottom.
The table
- Cohort: the month of each user's first event.
month_n: months since that: 0, 1, 2 and so on.- Month 0 must be 100%. If it is not, the join is wrong.
The query
MIN(ts)per user: one row per user, the cohort.- Join the activity: months between the activity month and the cohort month.
COUNT(DISTINCT user_id): users per cell, not events.
The cohort table, one query
One row per user for the cohort, one row per user and month for the activity, a join, a count. Postgres.
WITH cohorts AS (
SELECT user_id,
MIN(DATE_TRUNC('month', ts)) AS cohort
FROM events GROUP BY user_id
), activity AS (
SELECT DISTINCT user_id,
DATE_TRUNC('month', ts) AS month
FROM events
)
SELECT c.cohort,
EXTRACT(YEAR FROM AGE(a.month, c.cohort)) * 12
+ EXTRACT(MONTH FROM AGE(a.month, c.cohort))
AS month_n,
COUNT(DISTINCT a.user_id) AS users
FROM cohorts c JOIN activity a USING (user_id)
GROUP BY 1, 2 ORDER BY 1, 2;
To turn the counts into percentages, divide each cell by its cohort's month 0:
SELECT cohort, month_n, users,
ROUND(100.0 * users / FIRST_VALUE(users) OVER (
PARTITION BY cohort ORDER BY month_n), 1) AS pct
FROM cohort_counts;
where cohort_counts is the first query. In BigQuery, DATE_DIFF(a.month, c.cohort, MONTH) replaces the two EXTRACT lines.
Remember
- The triangle: a cohort from three months ago has no month 6 yet. Empty cells on the right are expected.
- The curve going up: only old cohorts reach month 10.
The trap: three ways the table lies
| You see | Cause | Fix |
|---|---|---|
| month 0 = 83% | cohort from signups, activity from events: 17% never fired an event | both from the same table, or call it activation |
| month 1 = 140% | COUNT(*) on events, not users | COUNT(DISTINCT user_id) |
| month 10 jumps up | only the 2025 cohorts are 10 months old | show n per cell, cut the tail |
The blended curve averages over whoever has reached that age. At month 10 that is the early adopters only. The curve did not improve, the mix did.
The other way a tail goes up is survivorship in the data itself: churned accounts get deleted, the users table shrinks, and a query that takes its denominator from the users table divides by fewer people every month. Keep the cohort size from the first query, computed once, and never from a table that loses rows.
The quiz
Which one gives month 0 = 100%?
A) SELECT DATE_TRUNC('month', created_at)
AS cohort, id AS user_id
FROM users;
B) SELECT MIN(DATE_TRUNC('month', ts))
AS cohort, user_id
FROM events
GROUP BY user_id;
B. In B the cohort is the month of each user's first event, so every user is active in their own month 0 by construction: 100%. In A the cohort comes from the signup date; users who signed up and never fired an event sit in the denominator and not in the numerator, so month 0 lands at 80 to 90%. A is not wrong, it measures activation. It is just not the table with 100% on the left.
The window function in the percentage query is on the window functions sheet.
Frequently asked questions
What is a cohort retention table?
How do I compute it in SQL?
Why is my month 0 not 100%?
Why does the overall retention curve go up at month 10?
Get the free PDF
One page, print-ready, free to share. No signup needed.