Cohort Analysis in SQL: Retention by Signup Month
· 7 min read
A cohort analysis groups customers by when they started, usually their signup month, and follows each group over time. It asks "of the people who signed up in January, how many still ordered one, two and three months later?"
The core of every cohort analysis SQL query is two columns per activity row: which cohort the customer belongs to, and how many months after the cohort's start the activity happened.
WITH cohorts AS (
SELECT user_id,
CAST(DATE_TRUNC('month', signup_date) AS DATE) AS cohort_month
FROM users
)
SELECT cohort_month, COUNT(*) AS users
FROM cohorts
GROUP BY cohort_month
ORDER BY cohort_month;This guide builds a small retention table in four steps, on tables you can see in full. One warning: the shape of a cohort table depends entirely on your data. The SQL tells you what happened in your data, not what is normal.
The example tables
| user_id | signup_date |
|---|---|
| 1 | 2025-01-04 |
| 2 | 2025-01-15 |
| 3 | 2025-01-28 |
| 4 | 2025-02-02 |
| 5 | 2025-02-19 |
| 6 | 2025-03-01 |
| 7 | 2025-03-12 |
| 8 | 2025-03-27 |
| order_id | user_id | order_date |
|---|---|---|
| 101 | 1 | 2025-01-05 |
| 102 | 1 | 2025-02-10 |
| 103 | 1 | 2025-04-02 |
| 104 | 2 | 2025-01-20 |
| 105 | 4 | 2025-02-03 |
| 106 | 4 | 2025-03-08 |
| 107 | 4 | 2025-04-15 |
| 108 | 5 | 2025-03-01 |
| 109 | 6 | 2025-03-03 |
| 110 | 6 | 2025-03-20 |
| 111 | 6 | 2025-04-09 |
| 112 | 7 | 2025-04-28 |
The data ends in April 2025. Users 3 and 8 never ordered.
Step 1: assign each user to a cohort
The query at the top truncates each signup date to its month, the user's cohort:
| cohort_month | users |
|---|---|
| 2025-01-01 | 3 |
| 2025-02-01 | 2 |
| 2025-03-01 | 3 |
These counts are the denominators for everything that follows. The cohort comes from users, not orders: built from orders, users who never ordered would vanish and every percentage would look better than it is.
DATE_TRUNC returns a timestamp in DuckDB and Postgres, hence the CAST (more on date functions).
Step 2: first order per user
How quickly does each cohort convert? A LEFT JOIN keeps users with no orders, and MIN(order_date) finds each user's first:
WITH first_orders AS (
SELECT u.user_id,
CAST(DATE_TRUNC('month', u.signup_date) AS DATE) AS cohort_month,
MIN(o.order_date) - u.signup_date AS days_to_first_order
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.user_id
GROUP BY u.user_id, u.signup_date
)
SELECT cohort_month,
COUNT(*) AS users,
COUNT(days_to_first_order) AS ever_ordered,
ROUND(AVG(days_to_first_order), 1) AS avg_days_to_first
FROM first_orders
GROUP BY cohort_month
ORDER BY cohort_month;| cohort_month | users | ever_ordered | avg_days_to_first |
|---|---|---|---|
| 2025-01-01 | 3 | 2 | 3 |
| 2025-02-01 | 2 | 2 | 5.5 |
| 2025-03-01 | 3 | 2 | 24.5 |
Date minus date gives days in DuckDB and Postgres. COUNT(days_to_first_order) skips users who never ordered; COUNT(*) counts everyone (more on COUNT).
The March average of 24.5 days comes from two people, one of whom took 47 days. In small cohorts, one customer moves the number a lot.
Step 3: activity by month number
Label every order with its month number: 0 for the signup month, 1 for the next, and so on.
WITH cohorts AS (
SELECT user_id,
CAST(DATE_TRUNC('month', signup_date) AS DATE) AS cohort_month
FROM users
),
activity AS (
SELECT c.cohort_month, o.user_id,
date_diff('month', c.cohort_month, o.order_date) AS month_number
FROM cohorts AS c
JOIN orders AS o ON o.user_id = c.user_id
)
SELECT cohort_month, month_number,
COUNT(*) AS orders,
COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;| cohort_month | month_number | orders | active_users |
|---|---|---|---|
| 2025-01-01 | 0 | 2 | 2 |
| 2025-01-01 | 1 | 1 | 1 |
| 2025-01-01 | 3 | 1 | 1 |
| 2025-02-01 | 0 | 1 | 1 |
| 2025-02-01 | 1 | 2 | 2 |
| 2025-02-01 | 2 | 1 | 1 |
| 2025-03-01 | 0 | 2 | 1 |
| 2025-03-01 | 1 | 2 | 2 |
March, month 0: two orders but one active user, because user 6 ordered twice. Retention counts people, so use COUNT(DISTINCT user_id). Counting rows would report 67% active when one of three users was.
Month numbers make cohorts comparable: January's month 1 is February and March's is April, but both mean "the month after signing up".
date_diff('month', a, b) is DuckDB and counts month boundaries crossed. Postgres has no date_diff; the portable version is (EXTRACT(YEAR FROM b) - EXTRACT(YEAR FROM a)) * 12 + EXTRACT(MONTH FROM b) - EXTRACT(MONTH FROM a). Avoid age(), which counts complete months.
Step 4: the retention table
Pivot month numbers into columns and divide by cohort size:
WITH cohorts AS (
SELECT user_id,
CAST(DATE_TRUNC('month', signup_date) AS DATE) AS cohort_month
FROM users
),
activity AS (
SELECT c.cohort_month, c.user_id,
date_diff('month', c.cohort_month, o.order_date) AS month_number
FROM cohorts AS c
LEFT JOIN orders AS o ON o.user_id = c.user_id
)
SELECT cohort_month,
COUNT(DISTINCT user_id) AS users,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 0 THEN user_id END) / COUNT(DISTINCT user_id)) AS m0_pct,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 1 THEN user_id END) / COUNT(DISTINCT user_id)) AS m1_pct,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 2 THEN user_id END) / COUNT(DISTINCT user_id)) AS m2_pct,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 3 THEN user_id END) / COUNT(DISTINCT user_id)) AS m3_pct
FROM activity
GROUP BY cohort_month
ORDER BY cohort_month;| cohort_month | users | m0_pct | m1_pct | m2_pct | m3_pct |
|---|---|---|---|---|---|
| 2025-01-01 | 3 | 67 | 33 | 0 | 33 |
| 2025-02-01 | 2 | 50 | 100 | 50 | 0 |
| 2025-03-01 | 3 | 33 | 67 | 0 | 0 |
The LEFT JOIN keeps users with no orders in the denominator. 100.0 avoids integer division in Postgres. The CASE pattern is covered in SQL CASE WHEN; DuckDB and Postgres also accept COUNT(DISTINCT user_id) FILTER (WHERE month_number = 1).
Retention does not always fall. January's month 3 (33%) beats its month 2 (0%) because user 1 came back in April. "Active in month N" is not "never left", so decide which one the question needs.
Don't report months you haven't seen yet
The table above has a quiet error. The data ends in April, so the March cohort's months 2 and 3 (May, June) have not happened, yet the query reports 0%. On a chart that looks like everyone churned. Return NULL for months the data has not reached:
WITH cohorts AS (
SELECT user_id,
CAST(DATE_TRUNC('month', signup_date) AS DATE) AS cohort_month
FROM users
),
activity AS (
SELECT c.cohort_month, c.user_id,
date_diff('month', c.cohort_month, o.order_date) AS month_number
FROM cohorts AS c
LEFT JOIN orders AS o ON o.user_id = c.user_id
),
data_end AS (
SELECT CAST(DATE_TRUNC('month', MAX(order_date)) AS DATE) AS last_month
FROM orders
)
SELECT a.cohort_month,
COUNT(DISTINCT a.user_id) AS users,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.month_number = 1 THEN a.user_id END) / COUNT(DISTINCT a.user_id)) AS m1_pct,
CASE WHEN date_diff('month', a.cohort_month, d.last_month) >= 2
THEN ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.month_number = 2 THEN a.user_id END) / COUNT(DISTINCT a.user_id))
END AS m2_pct,
CASE WHEN date_diff('month', a.cohort_month, d.last_month) >= 3
THEN ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.month_number = 3 THEN a.user_id END) / COUNT(DISTINCT a.user_id))
END AS m3_pct
FROM activity AS a
CROSS JOIN data_end AS d
GROUP BY a.cohort_month, d.last_month
ORDER BY a.cohort_month;| cohort_month | users | m1_pct | m2_pct | m3_pct |
|---|---|---|---|---|
| 2025-01-01 | 3 | 33 | 0 | 33 |
| 2025-02-01 | 2 | 100 | 50 | NULL |
| 2025-03-01 | 3 | 67 | NULL | NULL |
Now the table has the familiar triangle shape. A NULL says "not known yet", which is the truth; a 0 would be a claim. Taking the cut-off from MAX(order_date) moves it forward as data arrives. If the last month is only partly loaded, use the last complete month instead.
AI assistants often skip this step, because the query returns numbers either way. When you review a cohort query, check the newest cohort's oldest columns.
Reading the result honestly
With eight users, these percentages mean nothing beyond the example. In real data:
- Check cohort sizes first. A 100% from two people is a coincidence until a larger cohort repeats it.
- Compare cohorts on the same month number. Seasonality still plays a part: a December cohort's month 1 is January.
- Define "active" on purpose. Any order, a paid order and a login each give a different table. Filter
ordersin theactivitystep to match the question. - Choose the cohort definition. Signup month asks how well you keep people who sign up; first-order month asks how well you keep paying customers.
FAQ
How do I calculate retention rate in SQL?
Distinct users active in month N divided by all users in the cohort, as in step 4, with a LEFT JOIN so inactive users stay in the denominator.
Should cohorts be weekly or monthly?
Match how often people use the product. The SQL is the same with 'week' in place of 'month'.
You can practise cohort queries on a full year of one company's customers and orders on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “How many of each signup cohort bought” →