Fill missing dates in a report

Generate a list of every date you want to show, then LEFT JOIN the sales onto it, and turn the missing values into zero with COALESCE. The date list is called a date spine.

Query

WITH spine AS (
  SELECT d AS sale_date
  FROM generate_series(DATE '2025-03-01', DATE '2025-03-31', INTERVAL '1 day') AS g(d)
)
SELECT s.sale_date,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM spine s
LEFT JOIN orders o ON o.order_date = s.sale_date
GROUP BY s.sale_date
ORDER BY s.sale_date;

The date list has to be the left side of the join. If you start from orders and join the calendar, the days with no orders are exactly the rows that go missing.

Ways to build the spine

• Postgres: generate_series.
• BigQuery: UNNEST(GENERATE_DATE_ARRAY('2025-03-01', '2025-03-31')).
• Snowflake: TABLE(GENERATOR(ROWCOUNT => 31)) with DATEADD and a row number.
• Any engine: a recursive CTE, or a permanent dim_date table. A date dimension is the best answer when the warehouse has one, because it is correct, shared by everyone, and can carry holiday flags and fiscal calendars too.

Per-store or per-product spines

If the report is "sales per store per day", one date list is not enough, because a store that sold nothing still needs a row. Cross join the dates with the stores to get every combination, then left join the facts:

FROM spine s
CROSS JOIN stores st
LEFT JOIN orders o
  ON o.order_date = s.sale_date AND o.store_id = st.store_id

Check

The output should have exactly days times stores rows. Count them. Also keep the filter on the date range inside the spine, not in the WHERE after the join, so you do not lose the zero rows by accident.