Advanced
Case 44 of 50
Case 044: The Cohort Quality Check
customersorders
The cohort sizes from before look great on their own, but a big signup month means nothing if none of those people ever buy again.
The manager wants to know which cohorts actually stuck: "Cohort size alone doesn't tell me if a cohort is any good. Could you find out how many from each group actually became repeat customers?"
The data Alex is looking at
customers
| id | name | country | signup_date |
|---|---|---|---|
| 1 | Sam | Germany | 2026-01-02 |
| 2 | Nina | Canada | 2026-01-05 |
| 3 | David | Germany | 2026-01-10 |
| 4 | Maya | Japan | 2026-01-15 |
| 5 | Daniel | Canada | 2026-01-20 |
| … 5 more rows | |||
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“For each signup cohort month, show the cohort size and how many of those customers went on to place a second order.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH cohort AS (
SELECT id, DATE_FORMAT(signup_date, '%Y-%m') AS cohort_month
FROM customers
),
order_counts AS (
SELECT customer_id, COUNT(*) AS n
FROM orders
GROUP BY customer_id
)
SELECT
cohort.cohort_month,
COUNT(DISTINCT cohort.id) AS cohort_size,
COUNT(DISTINCT CASE WHEN order_counts.n >= 2 THEN cohort.id END) AS repeat_customers
FROM cohort
LEFT JOIN order_counts ON order_counts.customer_id = cohort.id
GROUP BY cohort.cohort_month
ORDER BY cohort.cohort_month;
What that query returns
Case Debrief
Cohort analysis with conditional COUNT DISTINCT
- COUNT(DISTINCT CASE WHEN ... END) is a compact way to count a filtered subset within a larger GROUP BY — no separate query needed.