Daily orders including zero days

The dashboard team is plotting daily orders. GROUP BY order_date only returns days that had an order, so quiet days vanish and the line chart draws straight across them.

Return one row for every calendar day from the first order date to the last order date, inclusive:

column	type	meaning
day	date	the calendar day
orders	integer	orders placed that day, any status; 0 if none

Sort by day, oldest first. Order matters.

day must be a DATE, not a timestamp.

Tables: orders.

Approach

Build a spine of every day in range, left-join the orders onto it, and count.

WITH bounds AS (
  SELECT MIN(order_date) AS first_day, MAX(order_date) AS last_day
  FROM orders
),
spine AS (
  SELECT CAST(d AS DATE) AS day
  FROM bounds,
       generate_series(first_day, last_day, INTERVAL 1 DAY) AS g(d)
)
SELECT s.day,
       COUNT(o.order_id) AS orders
FROM spine s
LEFT JOIN orders o ON o.order_date = s.day
GROUP BY s.day
ORDER BY s.day;

Why it works

The spine decides which rows exist. The data only decides the numbers in them. A LEFT JOIN keeps every spine day, and a day with no orders gets one row of NULLs from the right side.

COUNT(*) would count that NULL row as 1. COUNT(o.order_id) counts non-null values only, so the empty day is 0. This is the most common bug in date-spine queries.

The bounds come from the data, so the spine grows when a new order arrives after the current last day. generate_series includes its stop value. DuckDB’s range does not, which is an easy off-by-one.

Without the explicit ORDER BY, rows come back in whatever order the hash aggregate emits them.

Under the hood

generate_series is a table function: it streams 695 rows without reading any table. The join is a hash join with the spine on one side and orders on the other, keyed by date. The GROUP BY is a hash aggregate, and the final ORDER BY sorts 695 rows. In a warehouse you’d often keep a permanent calendar table instead, which also carries columns like week, month and holiday flags.