Monthly Buyer Cohort Retention — SQL Dates Problem
A customer's cohort is the calendar month of their first order.
- Difficulty: Hard
- Topics: Dates, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #117
Problem statement
A customer's cohort is the calendar month of their first order. The retention report asks, for each cohort, how many of its customers ordered again in the next calendar month and how many in the month after that (a customer counts once per column however many orders they placed, and may count in both).
Return cohort_month (YYYY-MM), customers (the cohort's size), month_1 and month_2, ordered by cohort_month. Orders later in the cohort month itself do not count as retention.
Tables
Table: orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| order_date | date |
| order_total | int |
Primary key: order_id.
One row per order.
Examples
Example 1
orders
| order_id | customer_id | order_date | order_total |
|---|---|---|---|
| 1 | 1 | 2023-12-05 | 899 |
| 2 | 1 | 2024-01-10 | 499 |
| 3 | 1 | 2024-02-02 | 1299 |
| 4 | 2 | 2023-12-20 | 650 |
| 5 | 2 | 2023-12-28 | 300 |
| 6 | 2 | 2024-02-15 | 799 |
| 7 | 3 | 2024-01-03 | 2499 |
| 8 | 3 | 2024-02-28 | 199 |
| 9 | 3 | 2024-02-29 | 349 |
| 10 | 4 | 2024-01-30 | 1500 |
Output
| cohort_month | customers | month_1 | month_2 |
|---|---|---|---|
| 2023-12 | 2 | 1 | 2 |
| 2024-01 | 2 | 1 | 0 |
How to solve Monthly Buyer Cohort Retention
Three steps. First, each customer's first order date — a grouped MIN — which fixes the cohort month. Second, compare every order of the customer with that first date on a month index: YEAR * 12 + MONTH turns calendar months into consecutive integers, so the difference is 1 for the next month even across a year boundary (December 2023 → January 2024). Day-based arithmetic would be wrong here: 30 January to 1 March is only 31 days, yet it is two calendar months on, not one.
Collapse to one row per customer with MAX(CASE …) flags: did they order at index +1, at index +2. Taking MAX rather than SUM is what makes customer 3, with two orders in February, count once. Index 0 (more orders in the cohort month, like customer 2's second December order) sets neither flag. Finally group the customers by cohort: COUNT(*) is the cohort size, the summed flags are the retained counts.
The alternative keeps one row per customer from the start and asks two EXISTS questions per customer, building the target month with DATE_ADD(…, INTERVAL n MONTH). Both do one pass to find cohorts and one look at each customer's orders.
Reference solution (MySQL)
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
GROUP BY customer_id
), per_customer AS (
SELECT f.customer_id, DATE_FORMAT(f.first_date, '%Y-%m') AS cohort_month,
MAX(CASE WHEN (YEAR(o.order_date) * 12 + MONTH(o.order_date)) - (YEAR(f.first_date) * 12 + MONTH(f.first_date)) = 1 THEN 1 ELSE 0 END) AS m1,
MAX(CASE WHEN (YEAR(o.order_date) * 12 + MONTH(o.order_date)) - (YEAR(f.first_date) * 12 + MONTH(f.first_date)) = 2 THEN 1 ELSE 0 END) AS m2
FROM firsts f
JOIN orders o ON o.customer_id = f.customer_id
GROUP BY f.customer_id, DATE_FORMAT(f.first_date, '%Y-%m')
)
SELECT cohort_month, COUNT(*) AS customers, SUM(m1) AS month_1, SUM(m2) AS month_2
FROM per_customer
GROUP BY cohort_month
ORDER BY cohort_monthAnother way
WITH cohorts AS (
SELECT customer_id, DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort_month FROM orders GROUP BY customer_id
)
SELECT c.cohort_month, COUNT(*) AS customers,
SUM(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
AND DATE_FORMAT(o.order_date, '%Y-%m') = DATE_FORMAT(DATE_ADD(CONCAT(c.cohort_month, '-01'), INTERVAL 1 MONTH), '%Y-%m')) THEN 1 ELSE 0 END) AS month_1,
SUM(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
AND DATE_FORMAT(o.order_date, '%Y-%m') = DATE_FORMAT(DATE_ADD(CONCAT(c.cohort_month, '-01'), INTERVAL 2 MONTH), '%Y-%m')) THEN 1 ELSE 0 END) AS month_2
FROM cohorts c
GROUP BY c.cohort_month
ORDER BY c.cohort_month← Shopping Sessions Abandoned After Add to Cart · Median Delivery Days per Courier Partner →