# Filling Gaps in Time Series — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/a-fill

> generate_series and LEFT JOIN.

## 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.

![Three ideas: gap filling, gaps and islands, deduplication.](assets/figures/sql-deep-dive/section-6-map.svg) — Figure 6.1 — Gap filling, islands and deduplication.

## 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.

```sql
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.

**Quiz:** How do you show days with no orders as zero?

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

*Answer:* LEFT JOIN orders to a generated list of days and COALESCE the sum to 0. Start from the complete calendar.