SQL Cohort Analysis
Group users by when they joined, then compare how each group performed.
What & Why
A cohort is a group of users who share a starting point in time — usually their signup month. Cohort analysis compares how different signup cohorts perform on the same metric, controlling for how long they've had to act.
See How It Works
BUSINESS QUESTION
Compare retained-user counts across signup cohorts and elapsed periods.
| 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_pct
FROM growth.cohort_retention
ORDER BY cohort_month, period_number;RESULT — cohort retention matrix rows
| cohort_month | period_number | cohort_users | retained | retention_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 |
Period number aligns each retained value with its original cohort.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario