SQL for Analysis · 12 min · 160 XP
Calendars and missing days
Report every day, including the ones that have no row.
The last lesson ended on a fact that matters more than it looks: a day with no sales often has no row at all. This lesson is about reporting those days anyway. It uses the same stores database.
A missing row isn't a zero
The website was down from 10 to 12 June 2025. Those days have no rows, so June has 27 website rows, not 30:
| day | orders | revenue |
|---|---|---|
| 2025-06-08 | 22 | 1386 |
| 2025-06-09 | 23 | 1265 |
| 2025-06-13 | 23 | 1242 |
| 2025-06-14 | 28 | 1736 |
The query
SELECT day, orders, revenue FROM daily_sales WHERE store_id = 1 AND day >= '2025-06-08' AND day < '2025-06-15' ORDER BY day;
A row that says 0 is something else. On 8 January 2025, Leeds opened through the snow and sold nothing, so that day has a row with 0 orders. Sunday the 5th, when it was closed, has no row:
| day | orders | revenue |
|---|---|---|
| 2025-01-04 | 13 | 1040 |
| 2025-01-06 | 10 | 750 |
| 2025-01-07 | 10 | 660 |
| 2025-01-08 | 0 | 0 |
| 2025-01-09 | 6 | 468 |
| 2025-01-10 | 7 | 553 |
| 2025-01-11 | 12 | 984 |
The query
SELECT day, orders, revenue FROM daily_sales WHERE store_id = 2 AND day >= '2025-01-04' AND day < '2025-01-12' ORDER BY day;
COUNT(*) counts the snow day, because it's a row, and skips the Sunday, because it isn't. An average over these rows treats the snow day as a bad day and the Sunday as if it never happened.
Make the days yourself
To report every day, including those with no row, make the days yourself. A recursive CTE produces a calendar, one row per day, and a LEFT JOIN hangs the sales on it. Days that found no sales get NULL, which COALESCE turns into 0.
WITH RECURSIVE calendar(day) AS ( SELECT '2025-06-08' UNION ALL SELECT date(day, '+1 day') FROM calendar WHERE day < '2025-06-14' ) SELECT c.day, COALESCE(s.revenue, 0) AS revenue FROM calendar AS c LEFT JOIN daily_sales AS s ON s.day = c.day AND s.store_id = 1 ORDER BY c.day; -- day revenue -- 2025-06-08 1386.0 -- 2025-06-09 1265.0 -- 2025-06-10 0 -- 2025-06-11 0 -- 2025-06-12 0 -- 2025-06-13 1242.0 -- 2025-06-14 1736.0
Filter in ON, not WHERE
Put the store filter in the ON, not in WHERE. On a day with no sales, every column from daily_sales is NULL, including store_id, so WHERE s.store_id = 1 throws out exactly the days the calendar was there to keep. The query still runs, and the gaps quietly disappear again.
Loading your workspace…