Advanced
Case 49 of 50
Case 049: Filling the Gaps
orders
Ops wants an audit of ByteMart's very first sales window, and the manager brings the question to Alex.
"January 5th to January 21st — our first real stretch of business. Was there a single day in there where absolutely nothing sold?"
The data Alex is looking at
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:
“Between 2026-01-05 and 2026-01-21 (inclusive), find every calendar day on which no order was placed at all.”
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 RECURSIVE calendar(day) AS (
SELECT '2026-01-05'
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY)
FROM calendar
WHERE day < '2026-01-21'
)
SELECT calendar.day AS quiet_day
FROM calendar
LEFT JOIN orders ON orders.order_date = calendar.day
WHERE orders.id IS NULL
ORDER BY calendar.day;
What that query returns
Case Debrief
Recursive CTE (generating a date series)
- WITH RECURSIVE lets a CTE build its own rows step by step, referencing itself.
- It's the standard way to generate a date series, a number sequence, or walk a hierarchy entirely in SQL, with no source table to start from.