How do you use a recursive CTE to generate a date series or number sequence when your database lacks a built-in generate_series function?

How to think about it

The question is really testing whether you know why date spines exist: without one, dates with no activity simply disappear from your result set, breaking line charts and period-over-period comparisons. A recursive CTE lets you manufacture every date in a range so your LEFT JOIN can keep them visible.

The three-part pattern

Every recursive CTE that generates a sequence has the same structure:

1. Anchor — seed the first value.
2. Recursive member — increment by one step and UNION ALL it back.
3. Termination — a WHERE condition that stops the loop.

WITH RECURSIVE nums AS (
    SELECT 1 AS n              -- anchor: start at 1
    UNION ALL
    SELECT n + 1               -- recursive: add 1
    FROM nums
    WHERE n < 10               -- termination: stop at 10
)
SELECT n FROM nums;

Building a date spine

The same pattern works for dates — just swap integers for date arithmetic:

WITH RECURSIVE date_spine AS (
    SELECT DATE('2024-01-01') AS dt          -- anchor: first day
    UNION ALL
    SELECT DATE(dt, '+1 day')                -- recursive: next day (SQLite syntax)
    FROM date_spine
    WHERE dt < DATE('2024-01-07')            -- termination: last day
)
SELECT dt FROM date_spine;

The real payoff — filling gaps in your fact table

The spine’s job is to be the left side of a LEFT JOIN so every date appears even when there’s no data:

WITH RECURSIVE date_spine AS (
    SELECT DATE('2024-01-01') AS dt
    UNION ALL
    SELECT DATE(dt, '+1 day') FROM date_spine WHERE dt < DATE('2024-01-07')
),
daily AS (
    SELECT sale_date, SUM(amount) AS revenue
    FROM sales
    GROUP BY sale_date
)
SELECT
    s.dt,
    COALESCE(d.revenue, 0) AS revenue   -- 0 instead of missing row
FROM date_spine s
LEFT JOIN daily d ON s.dt = d.sale_date
ORDER BY s.dt;

Try it live — note that Jan 4 and Jan 7 have no sales but still show up as 0:

Why this works

Each iteration of the recursive member sees only the rows produced by the previous iteration — not the whole accumulated result. So at pass 1, dt = '2024-01-01'; the engine computes DATE(dt, '+1 day') to get '2024-01-02', appends it, and repeats until the WHERE stops it. No stored table is touched — it’s pure computation.