Next-Month Retention of First-Order Cohorts — SQL Problem
A customer's cohort is the calendar month of their first order.
- Difficulty: Hard
- Topics: Dates, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #167
Problem statement
A customer's cohort is the calendar month of their first order. A customer is retained if they ordered again at any time in the calendar month right after their cohort month (a January customer must order in February; ordering again in January, or only in March, does not count).
Return one row per cohort with cohort_month (YYYY-MM), customers (the cohort's size), retained and retention_pct (retained as a percentage of customers, rounded to 1 decimal place). Order the rows by cohort_month.
Tables
Table: Orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| placed_at | datetime |
| order_value | int |
Primary key: order_id.
One row per delivered order.
Examples
Example 1
Orders
| order_id | customer_id | placed_at | order_value |
|---|---|---|---|
| 1 | 71 | 2024-01-04 13:00:00 | 300 |
| 2 | 71 | 2024-02-14 20:00:00 | 420 |
| 3 | 72 | 2024-01-20 19:30:00 | 250 |
| 4 | 72 | 2024-01-28 21:00:00 | 310 |
| 5 | 73 | 2024-01-31 23:00:00 | 199 |
| 6 | 73 | 2024-03-02 12:00:00 | 260 |
| 7 | 74 | 2024-12-30 20:00:00 | 500 |
| 8 | 74 | 2025-01-05 13:30:00 | 350 |
| 9 | 75 | 2024-12-12 19:00:00 | 280 |
| 10 | 76 | 2024-02-29 22:00:00 | 330 |
| 11 | 76 | 2024-03-01 09:00:00 | 150 |
Output
| cohort_month | customers | retained | retention_pct |
|---|---|---|---|
| 2024-01 | 3 | 1 | 33.3 |
| 2024-02 | 1 | 1 | 100 |
| 2024-12 | 2 | 1 | 50 |
How to solve Next-Month Retention of First-Order Cohorts
A cohort analysis is a chain of small steps, which is what CTEs are for. First find each customer's first order with MIN(placed_at) and reduce it to the first day of its month (DATE_FORMAT(…, '%Y-%m-01')). That date is the cohort.
Next, flag each customer: retained when an order exists in the half-open window [cohort + 1 month, cohort + 2 months). DATE_ADD(…, INTERVAL n MONTH) does the calendar arithmetic, including December → January of the next year (customer 74) and February's short month (customer 76, first order 29 February, retained by 1 March). A repeat order in the same month (customer 72) or a skip to the month after next (customer 73) is not in the window. EXISTS makes each customer count once however many orders they placed.
Finally group the flags by cohort: COUNT(*) is the size, SUM(kept) the retained, and their ratio × 100, rounded to one decimal, the percentage. Reducing every order to its distinct YYYY-MM and joining a cohort to the month exactly one later (TIMESTAMPDIFF(MONTH, …) = 1 between the first days) turns the flag into a LEFT JOIN instead. Each customer is looked up once with an index on (customer_id, placed_at).
Reference solution (MySQL)
WITH firsts AS (
SELECT customer_id, DATE_FORMAT(MIN(placed_at), '%Y-%m-01') AS cohort_start
FROM Orders
GROUP BY customer_id
), flagged AS (
SELECT f.customer_id, f.cohort_start,
CASE WHEN EXISTS (
SELECT 1 FROM Orders o
WHERE o.customer_id = f.customer_id
AND o.placed_at >= DATE_ADD(f.cohort_start, INTERVAL 1 MONTH)
AND o.placed_at < DATE_ADD(f.cohort_start, INTERVAL 2 MONTH)
) THEN 1 ELSE 0 END AS kept
FROM firsts f
)
SELECT LEFT(cohort_start, 7) AS cohort_month,
COUNT(*) AS customers,
SUM(kept) AS retained,
ROUND(100 * SUM(kept) / COUNT(*), 1) AS retention_pct
FROM flagged
GROUP BY LEFT(cohort_start, 7)
ORDER BY cohort_monthAnother way
WITH months AS (
SELECT DISTINCT customer_id, DATE_FORMAT(placed_at, '%Y-%m') AS ym FROM Orders
), cohort AS (
SELECT customer_id, MIN(ym) AS ym FROM months GROUP BY customer_id
)
SELECT c.ym AS cohort_month, COUNT(*) AS customers, COUNT(n.customer_id) AS retained,
ROUND(COUNT(n.customer_id) * 100 / COUNT(*), 1) AS retention_pct
FROM cohort c
LEFT JOIN months n ON n.customer_id = c.customer_id
AND TIMESTAMPDIFF(MONTH, CONCAT(c.ym, '-01'), CONCAT(n.ym, '-01')) = 1
GROUP BY c.ym
ORDER BY cohort_monthAnother way
WITH firsts AS (SELECT customer_id, MIN(placed_at) AS first_at FROM Orders GROUP BY customer_id)
SELECT DATE_FORMAT(f.first_at, '%Y-%m') AS cohort_month, COUNT(*) AS customers,
SUM(CASE WHEN (SELECT COUNT(*) FROM Orders o WHERE o.customer_id = f.customer_id
AND DATE_FORMAT(o.placed_at, '%Y-%m') = DATE_FORMAT(DATE_ADD(DATE_FORMAT(f.first_at, '%Y-%m-01'), INTERVAL 1 MONTH), '%Y-%m')) > 0
THEN 1 ELSE 0 END) AS retained,
ROUND(100 * SUM(CASE WHEN (SELECT COUNT(*) FROM Orders o WHERE o.customer_id = f.customer_id
AND DATE_FORMAT(o.placed_at, '%Y-%m') = DATE_FORMAT(DATE_ADD(DATE_FORMAT(f.first_at, '%Y-%m-01'), INTERVAL 1 MONTH), '%Y-%m')) > 0
THEN 1 ELSE 0 END) / COUNT(*), 1) AS retention_pct
FROM firsts f
GROUP BY DATE_FORMAT(f.first_at, '%Y-%m')
ORDER BY cohort_month← Longest On-Time Delivery Streak of Each Rider · Double-Logged Rider Shifts That Overlap →