Advanced
Case 47 of 50
Case 047: Unpredictable Buyers
customersorders
Restocking decisions assume customers order on a predictable schedule — but some don't, and that's quietly throwing off inventory planning.
The manager wants the two kinds separated: "Some customers order like clockwork. Others are all over the place, and it's throwing off our restocking schedule. Could you find out who's unpredictable? It's not about how often they buy — it's about how consistent the timing is — so we stop planning inventory around guesswork."
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 customers with at least 3 orders, find the average and standard deviation of the number of days between their orders — highest standard deviation (most irregular) first.”
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
)
SELECT
customers.name,
ROUND(AVG(gaps.gap_days), 1) AS avg_gap_days,
ROUND(STDDEV_SAMP(gaps.gap_days), 1) AS gap_stddev
FROM gaps
JOIN customers ON customers.id = gaps.customer_id
WHERE gaps.gap_days IS NOT NULL
GROUP BY customers.name
HAVING COUNT(gaps.gap_days) >= 2
ORDER BY gap_stddev DESC;
What that query returns
Case Debrief
STDDEV_SAMP as a custom aggregate
- STDDEV_SAMP() quantifies consistency, not just average behavior — two customers can share the same average order gap while one is perfectly regular and the other is wildly unpredictable.
- Quick tip: STDDEV_SAMP() uses that exact name in most major databases — SQL Server is the one exception:
- Postgres / MySQL / Snowflake: STDDEV_SAMP(x) — same name
- SQL Server: STDEV(x) — spelled with a single D (not STDDEV), but computes the same sample-stddev calculation