Generated series & LEFT JOIN

Fill the gaps in a timeline

Lesson 14 of 20 · 11 min · Module 4: Hierarchies, series and JSON

A chart of orders per day should show a zero for quiet days, but GROUP BY only returns days that have orders. Generating every day first, then joining the data to it, fills the gaps.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Generate a list of dates
Join real data onto a complete series
Report zeros instead of missing rows

The idea

A recursive CTE can generate a series: start with the first date and add one day with date(day, '+1 day') until the last date. LEFT JOIN the real data onto the series and count with COUNT(o.id).

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-03'
)
SELECT day FROM days;

01Missing is not zero

A report that skips quiet days makes a chart connect busy days directly, hiding the gaps. Generating the series guarantees one row per day.

02date() does the calendar

date('2026-10-31', '+1 day') returns 2026-11-01, so month ends and leap years are handled for you.

SELECT date('2028-02-28', '+1 day');

Output

2028-02-29

03The series is the left table

Because days comes first in the LEFT JOIN, every generated day stays in the result even when no order matches it.

Your turn

Return every day from 2026-10-01 to 2026-10-07 with the number of orders placed that day as orders (any status), including days with 0. Sort by day.

What the checks look for

Shows all seven days, including the quiet ones

Try it in the editor →

Take this with you

Generate the full timeline first, then attach the data.

Go a little further

Generate one row per month from 2026-08 to 2026-12 and show revenue per month with zeros for future months.