Intermediate
Case 31 of 50
Case 031: Did They Come Back?
customersorders
"Cohort sizes are nice,"
the manager says, "but I want to know if they actually stuck around. Of everyone who joined in January, how many ordered again in February?"
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:
“Count how many customers who signed up in January 2026 went on to place an order in February 2026.”
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 jan_signups AS (
SELECT id
FROM customers
WHERE DATE_FORMAT(signup_date, '%Y-%m') = '2026-01'
)
SELECT COUNT(DISTINCT orders.customer_id) AS retained_customers
FROM orders
JOIN jan_signups ON jan_signups.id = orders.customer_id
WHERE DATE_FORMAT(orders.order_date, '%Y-%m') = '2026-02';
What that query returns
Case Debrief
Retention (cohort + follow-up activity)
- Retention analysis is a cohort filter (who joined when) combined with a follow-up activity filter (did they act again).
- COUNT(DISTINCT orders.customer_id) counts each returning customer once, even if they placed several orders in February — plain COUNT(orders.customer_id) would count every one of those orders separately and inflate the number.