Intermediate
Case 35 of 50
Case 035: The Churn Signal
customersorders
"Some customers have just stopped ordering,"
the manager says. "As of today, June 25th, who's been silent for more than 60 days? I want to reach out before we lose them for good."
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:
“As of 2026-06-25, find every customer whose most recent order was more than 60 days ago, showing their last order date and how many days it's been.”
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 last_order AS (
SELECT customer_id, MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
)
SELECT
customers.name,
last_order.last_order_date,
DATEDIFF('2026-06-25', last_order.last_order_date) AS days_since_last_order
FROM customers
JOIN last_order ON last_order.customer_id = customers.id
WHERE DATEDIFF('2026-06-25', last_order.last_order_date) > 60
ORDER BY days_since_last_order DESC;
What that query returns
Case Debrief
Date arithmetic with DATEDIFF()
- DATEDIFF(end, start) is how MySQL computes date differences — it returns the number of days from start to end directly, no manual conversion needed.
- This case pins the reference date to '2026-06-25' so the expected answer stays fixed no matter when you run it. To use today's date instead in a real report:
- MySQL: CURDATE()
- SQL Server: GETDATE()
- Snowflake / Postgres: CURRENT_DATE
- Quick tip: DATEDIFF itself isn't standard everywhere — here's the same day-difference calculation in other major databases:
- SQL Server / Snowflake: DATEDIFF(day, start, end) — 3 arguments, unit first
- Postgres: (end::date - start::date) — no DATEDIFF function at all; dates just subtract directly