Recursive CTEs and date spines

A CTE that refers to itself, for generating every date in a range and walking hierarchies of any depth.

Every query so far has worked with rows that already exist. Sometimes you need rows that don’t: every month of the year, including empty ones, or every level of an org chart, however deep it goes. A recursive CTE builds them by repeating a step until a condition says stop.

The shape

WITH RECURSIVE numbers(n) AS (
    SELECT 1                  -- 1. the starting row
    UNION ALL
    SELECT n + 1              -- 2. the step: make a new row from the last one
    FROM numbers              --    (it reads from itself)
    WHERE n < 5               -- 3. the stop condition
)
SELECT n FROM numbers;        -- 1, 2, 3, 4, 5

The database runs the starting query, then the step on those rows, then the step on the new rows, until a round produces nothing.

Forget the stop condition, and it never ends. In the sandbox, try it: it stops reading after 10,000 rows, or stops the query after 5 seconds. On a real database, it runs until something kills it.

Date spines: every date, even empty ones

Lesson 31 hit this problem: no deals were won in May 2026, so May had no row, and LAG compared June with April. A date spine is a list of every period in a range, generated so none can go missing:

WITH RECURSIVE months(month_start) AS (
    SELECT '2026-01-01'
    UNION ALL
    SELECT date(month_start, '+1 month')
    FROM months
    WHERE month_start < '2026-12-01'
)
SELECT month_start FROM months;        -- 12 rows, 2026-01-01 to 2026-12-01

Then LEFT JOIN your data onto it, so every month appears and the empty ones get 0:

WITH RECURSIVE months(month_start) AS (
    SELECT '2026-01-01'
    UNION ALL
    SELECT date(month_start, '+1 month')
    FROM months
    WHERE month_start < '2026-10-01'
),
won AS (
    SELECT strftime('%Y-%m', closed_at) AS month,
           SUM(amount) AS amount
    FROM opportunities
    WHERE is_won = 1
    GROUP BY month
)
SELECT strftime('%Y-%m', m.month_start) AS month,
       COALESCE(w.amount, 0) AS won,
       LAG(COALESCE(w.amount, 0)) OVER (ORDER BY m.month_start) AS prev_month
FROM months AS m
LEFT JOIN won AS w ON w.month = strftime('%Y-%m', m.month_start);

May is back with 0, and June now compares with May. This is the fix for lesson 31’s LAG gap and lesson 32’s moving average, and it’s why charts built on a spine don’t skip empty periods. Generate the spine with the start of each month, so '+1 month' never hits the month-end overflow from lesson 21.

Walking a hierarchy

The same idea walks an org chart. Start with whoever has no manager, then repeatedly find the people who report to the rows found last time:

WITH RECURSIVE chain(user_id, name, level) AS (
    SELECT user_id, name, 1
    FROM users
    WHERE manager_id IS NULL                 -- start at the top
    UNION ALL
    SELECT u.user_id, u.name, c.level + 1
    FROM users AS u
    JOIN chain AS c ON u.manager_id = c.user_id   -- people who report to the last level
)
SELECT name, level
FROM chain
ORDER BY level, name;

Lesson 27’s self join could only go one level up. This goes as deep as the chart does, with no change to the query. The stop condition here is built in: when nobody reports to the last level, the join returns no rows. Bad data, like two people listed as each other’s manager, would loop forever, which is why real hierarchy queries often add WHERE c.level < 20 as a safety net.

Common mistakes

Dialect notes

Snowflake notes

Snowflake accepts WITH RECURSIVE (or plain WITH). For date spines, most Snowflake code skips recursion and uses GENERATOR:

SELECT DATEADD('month', SEQ4(), '2026-01-01')::DATE AS month_start
FROM TABLE(GENERATOR(ROWCOUNT => 12));

dbt projects usually build one permanent dim_date table with the dbt_utils.date_spine macro and join to it everywhere. For hierarchies, Snowflake also has CONNECT BY (lesson 27).

More in the field guide: Recursive CTE and Date spine. Exercise A6 builds a full rep-by-month grid by crossing a spine with users (lesson 27).

Try it

Show every month of 2026, January to December, with the number of deals created that month. Months with no deals must show 0. Return the month as YYYY-MM and the count, in month order.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson