Filling Gaps in Time Series

Days with no rows still exist

Grouping orders by day only returns days that have orders, so charts skip empty days. Generate the full calendar with generate_series (or a calendar table), LEFT JOIN the facts to it and COALESCE missing values to 0. The same technique fills missing hours, months or (product, day) combinations with a cross join of dimensions.

Recipes for common questions

Gap filling, streak detection and deduplication come up in almost every analytics codebase.

Daily revenue including empty days, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Every day from 26 February to 3 March appears; the cancelled order on 1 March is included because this query does not filter by status.

SELECT d::date AS day, COALESCE(sum(o.amount), 0) AS revenue
FROM generate_series(date '2026-02-26', date '2026-03-03', interval '1 day') AS d
LEFT JOIN orders o ON o.ordered_on = d::date
GROUP BY d ORDER BY d;

Output:

    day     | revenue 
------------+---------
 2026-02-26 |       0
 2026-02-27 |       0
 2026-02-28 |  700.00
 2026-03-01 |  150.00
 2026-03-02 |       0
 2026-03-03 |       0
(6 rows)

Keep a calendar table

A permanent calendar table with holidays and fiscal periods is reusable across reports.

Quick check: How do you show days with no orders as zero?

• Use INNER JOIN
• GROUP BY ordered_on only
• LEFT JOIN orders to a generated list of days and COALESCE the sum to 0
• Add ORDER BY day