SQL Retention Rate
Measure the share of each signup cohort retained after every elapsed period.
What & Why
Cohort retention divides the stored retained count by the cohort's original users count for the same cohort_month and period_number.
Keeping the starting cohort size as the denominator produces a comparable retention curve without redefining retention as one minus a different churn calculation.
See How It Works
BUSINESS QUESTION
Return the retention curve for every signup cohort and period.
| cohort_month | period_number | users | retained |
|---|---|---|---|
| 2024-01-01 | 0 | 420 | 420 |
| 2024-01-01 | 1 | 420 | 268 |
| 2024-01-01 | 2 | 420 | 214 |
| 2024-02-01 | 0 | 510 | 510 |
EXAMPLE QUERY
SELECT
cohort_month,
period_number,
users AS cohort_users,
retained,
ROUND(retained::numeric / NULLIF(users, 0) * 100, 2) AS retention_rate_pct
FROM growth.cohort_retention
ORDER BY cohort_month, period_number;RESULT — cohort retention
| cohort_month | period_number | cohort_users | retained | retention_rate_pct |
|---|---|---|---|---|
| 2024-01-01 | 0 | 420 | 420 | 100.00 |
| 2024-01-01 | 1 | 420 | 268 | 63.81 |
| 2024-01-01 | 2 | 420 | 214 | 50.95 |
| 2024-02-01 | 0 | 510 | 510 | 100.00 |
Each retained count is divided by its stored cohort size.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario