Advanced
Case 48 of 50
Case 048: The Customer Who Was Due
customersorders
"Averages and standard deviations are nice,"
the manager says, "but could you turn that into something actionable? Tell me who's actually overdue for their next order."
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 time since their last order already exceeds their own typical (average) gap between orders — flag them as overdue, showing their last order date and average gap.”
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 gaps AS (
SELECT
customer_id,
order_date,
DATEDIFF(
order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
) AS gap_days
FROM orders
),
customer_rhythm AS (
SELECT
customer_id,
AVG(gap_days) AS avg_gap_days,
MAX(order_date) AS last_order_date
FROM gaps
WHERE gap_days IS NOT NULL
GROUP BY customer_id
HAVING COUNT(gap_days) >= 2
)
SELECT
customers.name,
ROUND(customer_rhythm.avg_gap_days, 1) AS avg_gap_days,
customer_rhythm.last_order_date,
CAST(
DATEDIFF('2026-06-25', customer_rhythm.last_order_date) - customer_rhythm.avg_gap_days
AS SIGNED
) AS days_overdue
FROM customer_rhythm
JOIN customers ON customers.id = customer_rhythm.customer_id
WHERE DATEDIFF('2026-06-25', customer_rhythm.last_order_date) > customer_rhythm.avg_gap_days
ORDER BY days_overdue DESC;
What that query returns
Case Debrief
Predictive flagging (LAG + AVG + date math)
- Predictive flags don't need machine learning — comparing current behavior against a customer's own historical average, entirely in SQL, gets you surprisingly far.
- Quick tip: CAST(... AS SIGNED) truncates toward zero rather than rounding — 4.5 becomes 4, not 5. ROUND(..., 0) would round to the nearest whole day instead, which would overstate how overdue someone is (rounding 4.5 up to 5 claims a full 5th day has passed when it hasn't). Truncating only counts full, completed days past the average gap, which is what "overdue" should mean.