AI Hints
Homeโ€บSQL Tutorialโ€บPivots, Lists, and Recursion
SQL ยท Lesson 111 of 126

Filling Gaps in Dates

A calendar from nothing.

โฑ 2 min readโ–ถ Video at 2:36:18๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Filling Gaps in Dates
From the video: Filling Gaps in Dates
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    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.

โœ๏ธ Exercise: Quiet days in January

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
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Final Project: The Year in Review โ€” everything together