SQL Recursive CTE

A CTE that references itself — built for data a normal query can't walk on its own.

What & Why

A recursive CTE runs in two parts: an anchor query that picks a starting point, and a recursive query that repeats, each time building on the previous round's result — until a round produces nothing new.

It's the tool for hierarchical data: an org chart, a category tree, a comment thread's replies — anything a single-pass query can't walk on its own because you don't know in advance how many levels deep it goes.

See How It Works

BUSINESS QUESTION

Generate every calendar day between the earliest and latest campaign start dates.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

Watch a recursive CTE append one calendar row per iterationStep 1 of 3

anchor 2024-01-15 · add 1 day until 2024-04-01

iteration	day	last_day	state
anchor	2024-01-15	2024-04-01	CURRENT

The anchor reads the earliest and latest marketing.campaigns start dates.

EXAMPLE QUERY

WITH RECURSIVE bounds AS (
  SELECT MIN(start_date) AS first_day, MAX(start_date) AS last_day
  FROM marketing.campaigns
),
date_spine(day, last_day) AS (
  SELECT first_day, last_day FROM bounds
  UNION ALL
  SELECT day + 1, last_day
  FROM date_spine
  WHERE day < last_day
)
SELECT day
FROM date_spine
ORDER BY day;

This lesson's practice is part of Pro.

See what's included →

Advanced business practice