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:

The website, 8–14 June 2025
dayordersrevenue
2025-06-08221386
2025-06-09231265
2025-06-13231242
2025-06-14281736
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:

Leeds, 4–11 January 2025
dayordersrevenue
2025-01-04131040
2025-01-0610750
2025-01-0710660
2025-01-0800
2025-01-096468
2025-01-107553
2025-01-1112984
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.

sql
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…