Filling Gaps in Dates
A calendar from nothing.
Here's a problem with daily reports. If no orders came in on a day, that day simply doesn't appear. Your chart skips it, and it looks like nothing's wrong. To show zeros, you need a list of every date. A recursive CTE can build one.

- Quiet days disappear
- Charts skip them
- Build every date
A calendar with zeros
The recursive part adds one day at a time, from the first to the tenth of January. Then we left join orders onto the calendar, so every day appears, even with no orders. Run it. Now we can see the quiet days, with zero orders, like the second and the third.
WITH RECURSIVE days(day) AS (
SELECT '2024-01-01'
UNION ALL
SELECT date(day, '+1 day') FROM days WHERE day < '2024-01-10'
)
SELECT d.day, COUNT(o.order_id) AS orders
FROM days AS d
LEFT JOIN orders AS o ON o.order_date = d.day
GROUP BY d.day;โโโโโโโโโโโโโโฌโโโโโโโโโ โ day โ orders โ โโโโโโโโโโโโโโผโโโโโโโโโค โ 2024-01-01 โ 3 โ โ 2024-01-02 โ 0 โ โ 2024-01-03 โ 0 โ โ 2024-01-04 โ 1 โ โ 2024-01-05 โ 1 โ โ 2024-01-06 โ 0 โ โ 2024-01-07 โ 1 โ โ 2024-01-08 โ 0 โ โ 2024-01-09 โ 0 โ โ 2024-01-10 โ 1 โ โโโโโโโโโโโโโโดโโโโโโโโโ
Quiet days in January
Your turn. How many days in January twenty twenty four had no orders at all? Pause and try. Here's a solution. Build all thirty one days, left join orders, and count where there's no match. Thirteen quiet days.
Days in January 2024 with zero orders
Show hint
Calendar CTE to '2024-01-31', LEFT JOIN, WHERE o.order_id IS NULL
Show solution
WITH RECURSIVE days(day) AS (
SELECT '2024-01-01'
UNION ALL
SELECT date(day, '+1 day') FROM days WHERE day < '2024-01-31'
)
SELECT COUNT(*) AS days_without_orders
FROM days AS d
LEFT JOIN orders AS o ON o.order_date = d.day
WHERE o.order_id IS NULL;โโโโโโโโโโโโโโโโโโโโโโโ โ days_without_orders โ โโโโโโโโโโโโโโโโโโโโโโโค โ 13 โ โโโโโโโโโโโโโโโโโโโโโโโ
Summary
A calendar CTE plus a left join shows every day, including the zeros. Now you're ready for the final project.
Key takeaways- Calendar CTE
- LEFT JOIN the data
- Zeros show up