6-month learning planM04 · Databases & SQL · session 40 / 130 · DE40 / 130

data engineeringbeginner 15m read

S040 · CTEs & Recursive Queries

Compose complex SQL like Python functions.

Module M04: Databases & SQL · Session 40 of 130 · Track: DE 🧱

What you'll be able to do after this session

Explain CTEs & Recursive Queries to a friend in one minute, out loud, no notes.
Recognise it when you see it in real code / systems / papers.
Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

S039 — Window Functions — the Game-Changer

Pre-read (skim before the session)

🎥 Intuition (5–15 min) · CTE Explained — Kevin Stratvert (WITH clauses) — 10 min, from zero to "why WITH is nicer than subqueries."
🎥 Deep dive (30–60 min) · Recursive CTEs in PostgreSQL — EnterpriseDB — deep on the WITH RECURSIVE execution model & tree traversal.
🎥 Hands-on demo (10–20 min) · SQL Recursive Query Explained — Data with Baraa — types a real org-chart query end to end.
📖 Canonical article · PostgreSQL Docs — WITH Queries (Common Table Expressions) — the authoritative reference; includes recursive examples.
📖 Book chapter / tutorial · Mode Analytics — CTEs Tutorial — interactive walkthrough with realistic queries.
📖 Engineering blog · dbt Docs — Best Practices: Modular SQL with CTEs — how the modern analytics-engineering stack organises SQL around CTEs.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: Compose complex SQL like Python functions.

A big SQL query is like a big Python function with no helper functions — every operation crammed into one 200-line expression, nested subqueries three levels deep, impossible to read. A CTE (Common Table Expression, spelled WITH … AS (…)) is the SQL equivalent of extracting a helper: you name an intermediate result and reuse it. WITH recent_orders AS (SELECT … WHERE order_date > …), big_spenders AS (SELECT … FROM recent_orders GROUP BY user_id HAVING …) SELECT * FROM big_spenders JOIN users …. Each step reads top to bottom, each stage has a name, each is independently reviewable. That's it — the whole feature.

The bonus is the recursive CTE (WITH RECURSIVE) — the only way in pure SQL to traverse trees and graphs. Org charts, category hierarchies, folder structures, dependency graphs, "shortest path" in a small graph — all done with a base case + recursive step, exactly like a functional-programming recursion but expressed in SQL. Before recursive CTEs, this needed a WHILE loop in application code or a stored procedure.

Modern data teams (dbt, Looker, LookML, sqlmesh) have made CTEs the default style — every model is a chain of small CTEs, each named, each testable. If you learn SQL from a dbt codebase, you'll never see a subquery again.

The forever gotcha: CTEs used to be an "optimisation fence" in Postgres — they were always materialised (execution boundary). Since Postgres 12, non-recursive CTEs are inlined by default. On older versions (Postgres 11, older Redshift), a bad CTE can silently kill performance. Check your engine's docs.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Simple CTE — same query, two styles.

Ugly nested subquery:

SELECT dept, avg_sal FROM (
    SELECT dept, AVG(salary) AS avg_sal FROM employees
    WHERE hired > '2020-01-01' GROUP BY dept
) t
WHERE avg_sal > 90000
ORDER BY avg_sal DESC;

Same query with a CTE:

WITH recent_hires AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM   employees
    WHERE  hired > '2020-01-01'
    GROUP BY dept
)
SELECT dept, avg_sal
FROM   recent_hires
WHERE  avg_sal > 90000
ORDER BY avg_sal DESC;

Same execution plan, night-and-day readability.

Multiple CTEs — the analytics-engineering pattern:

WITH
  active_users AS (
    SELECT user_id FROM events WHERE event_date > current_date - 30
    GROUP BY user_id
  ),
  user_revenue AS (
    SELECT user_id, SUM(amount) AS rev FROM orders
    WHERE order_date > current_date - 30
    GROUP BY user_id
  ),
  scored AS (
    SELECT u.user_id, u.rev,
           NTILE(10) OVER (ORDER BY u.rev DESC) AS revenue_decile
    FROM active_users a JOIN user_revenue u USING (user_id)
  )
SELECT * FROM scored WHERE revenue_decile = 1;

Reads top to bottom like a paragraph. Each stage has one clear responsibility.

Recursive CTE — the classic org chart.

Table employees(id, name, manager_id):

id	name	manager_id
1	Satya	NULL
2	Rajesh	1
3	Priya	1
4	Alice	2
5	Bob	2
6	Carol	3
7	Dan	4

Find everyone in Satya's reporting tree, with depth:

WITH RECURSIVE reports AS (
    -- base case: the root
    SELECT id, name, manager_id, 0 AS depth
    FROM   employees
    WHERE  id = 1
    UNION ALL
    -- recursive step: children of anyone already in `reports`
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM   employees e
    JOIN   reports r ON e.manager_id = r.id
)
SELECT * FROM reports ORDER BY depth, id;

Execution:

Result (7 rows, depth 0–3). Same pattern powers file-tree walks, dependency graphs, and BOM (bill-of-materials) queries.

Recursive CTE anatomy — memorise these four parts:

Part	Purpose
`WITH RECURSIVE name AS (…)`	Declare it as recursive
Base query (`SELECT …`)	The starting rows — must terminate someday
`UNION ALL`	Combines base + each recursive iteration
Recursive query (`SELECT … FROM other JOIN name …`)	References the CTE itself; produces next iteration

Terminates when the recursive step returns zero rows. Always add a safety guard: WHERE depth < 100 to prevent runaway on cycles.

Recursive number generator (no source table needed):

WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n, n*n AS square FROM nums;

Also good for generating date series in databases without generate_series.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Save as cte_lab.py. SQLite 3.8+ supports recursive CTEs.

import sqlite3
conn = sqlite3.connect(":memory:"); cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER, salary INTEGER, hired TEXT
);
INSERT INTO employees VALUES
    (1, 'Satya',  NULL, 5000000, '2014-02-04'),
    (2, 'Rajesh',    1, 2200000, '2015-05-10'),
    (3, 'Priya',     1, 2100000, '2016-08-15'),
    (4, 'Alice',     2,  600000, '2020-03-01'),
    (5, 'Bob',       2,  450000, '2021-06-15'),
    (6, 'Carol',     3,  550000, '2019-01-10'),
    (7, 'Dan',       4,  180000, '2023-11-05');
""")
 
def run(label, sql):
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print("  ", row)
 
# 1. Basic CTE — same query as subquery, more readable
run("1. CTE — depts with avg salary > 500k (hired after 2019)", """
    WITH recent AS (
        SELECT manager_id, AVG(salary) AS avg_sal
        FROM employees WHERE hired > '2019-01-01'
        GROUP BY manager_id
    )
    SELECT * FROM recent WHERE avg_sal > 400000;""")
 
# 2. Multi-CTE chain — the analytics-engineering pattern
run("2. Multi-CTE — top-decile earners with manager name attached", """
    WITH decile AS (
        SELECT id, name, salary, manager_id,
               NTILE(3) OVER (ORDER BY salary DESC) AS band
        FROM employees
    ),
    top_band AS (SELECT * FROM decile WHERE band = 1)
    SELECT t.name, t.salary, m.name AS manager
    FROM   top_band t LEFT JOIN employees m ON m.id = t.manager_id
    ORDER BY t.salary DESC;""")
 
# 3. Recursive CTE — full org tree with depth
run("3. Recursive — everyone under Satya, with depth", """
    WITH RECURSIVE reports AS (
        SELECT id, name, manager_id, 0 AS depth,
               CAST(name AS TEXT) AS path
        FROM employees WHERE id = 1
        UNION ALL
        SELECT e.id, e.name, e.manager_id, r.depth + 1,
               r.path || ' > ' || e.name
        FROM employees e
        JOIN reports r ON e.manager_id = r.id
        WHERE r.depth < 10    -- safety guard
    )
    SELECT depth, name, path FROM reports ORDER BY depth, id;""")
 
# 4. Recursive — reverse direction: find chain of managers for Dan
run("4. Recursive — Dan's management chain up to the CEO", """
    WITH RECURSIVE chain AS (
        SELECT id, name, manager_id, 0 AS level FROM employees WHERE name = 'Dan'
        UNION ALL
        SELECT e.id, e.name, e.manager_id, c.level + 1
        FROM employees e JOIN chain c ON e.id = c.manager_id
    )
    SELECT level, name FROM chain ORDER BY level;""")
 
# 5. Recursive number generator — no source table
run("5. Recursive number gen — 1..10 and their squares", """
    WITH RECURSIVE nums(n) AS (
        SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 10
    )
    SELECT n, n*n AS square FROM nums;""")
 
# 6. Recursive date series — every day in Jan 2026
run("6. Recursive date series — all days in Jan 2026", """
    WITH RECURSIVE cal(d) AS (
        SELECT DATE('2026-01-01')
        UNION ALL
        SELECT DATE(d, '+1 day') FROM cal WHERE d < DATE('2026-01-31')
    )
    SELECT d FROM cal;""")
 
# 7. Total headcount under each manager (recursive summarisation)
run("7. Headcount under each manager (rollup)", """
    WITH RECURSIVE tree AS (
        SELECT id AS root, id, name FROM employees
        UNION ALL
        SELECT t.root, e.id, e.name
        FROM tree t JOIN employees e ON e.manager_id = t.id
    )
    SELECT r.name AS manager, COUNT(*) - 1 AS reports_count
    FROM tree t JOIN employees r ON r.id = t.root
    GROUP BY t.root, r.name
    HAVING reports_count > 0
    ORDER BY reports_count DESC;""")
 
conn.close()

Checklist — observe when you run:

Query 1 shows the CTE feels like a helper variable — try inlining it as a subquery, compare readability.
Query 2 chains two CTEs. Notice you can JOIN a CTE with a regular table freely.
Query 3 traverses 4 levels; the path column shows the breadcrumb Satya > Rajesh > Alice > Dan.
Query 4 walks up the tree instead of down — same recursive pattern, reversed join direction.
Query 5 generates 1–10 without any source table. Useful for backfilling missing dates.
Query 6: 31 rows for January. Try changing '+1 day' to '+1 hour' for hourly buckets.
Query 7 rolls up total reports under each manager. Satya = 6 reports, Rajesh = 3, Priya = 1, Alice = 1.
Comment out the WHERE r.depth < 10 safety guard, then add a cyclic row (UPDATE employees SET manager_id = 4 WHERE id = 1; — Satya reports to Alice who reports to Rajesh who reports to Satya). Query 3 now runs forever. Always guard.

Try this modification: add a categories(id, name, parent_id) table with 3 levels (Electronics → Phones → Smartphones). Write a recursive CTE that, for every category, computes its full path (e.g., "Electronics / Phones / Smartphones"). Bonus: count products in each category including all descendants.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

CTEs are the modern SQL style. Look at any real dbt project on GitHub — every model file is a chain of WITH … AS (…) blocks, one per logical step, with the final SELECT at the bottom. Airbnb's Minerva metrics layer, Netflix's data platform, Uber's Presto queries — all CTE-first. Nested subqueries in modern SQL code are a code smell.

Recursive CTEs quietly power huge features:

File systems / cloud storage. Google Drive, Dropbox, S3 folder trees — a single recursive CTE returns everything under a folder path.
Org charts, RBAC, and permission trees. "Show me all users who inherit permission P" is exactly the Query 3 pattern.
Bill-of-materials. Manufacturing (Boeing, Tesla) — "what raw materials make up this assembly?" is recursive tree traversal.
Category hierarchies. Amazon browse tree, marketplace taxonomies — recursive CTE with a materialised path or nested-set fallback.
Package dependency resolvers. Simple case: recursive CTE. Full case: needs SAT solver, but recursive CTE gets you 90% there for read-only queries.

War story — the CTE optimisation fence. In Postgres 9 & 10, every CTE was an execution boundary — WITH tiny AS (SELECT * FROM huge WHERE id = 42) SELECT * FROM tiny would materialise the whole tiny table before probing. A team migrated queries from subqueries to CTEs for readability and watched a critical dashboard slow by 30×. Postgres 12+ inlines non-recursive CTEs by default. If you're on Redshift (fork of Postgres 8.0), you still hit this. Check EXPLAIN.

War story — the recursive cycle disaster. A company's ORG data had a data-entry bug: two employees listed each other as manager. The nightly recursive report ran for hours, produced hundreds of millions of rows, filled the disk, and paged the on-call. The fix: WHERE depth < 20 in the recursive step and WHERE NOT id = ANY(cycle_check_array) (Postgres) to detect cycles. Both are cheap; both were missing.

Gotchas top teams handle:

Column types must match between base and recursive queries. SELECT 1 vs SELECT n+1 — one is int, one might promote to bigint. Cast explicitly.
UNION vs UNION ALL in recursive CTEs. UNION deduplicates on every iteration (expensive); UNION ALL doesn't (fast). Use UNION ALL + explicit visited-set for cycle detection.
Depth safety guard. Always add WHERE depth < N — cheap insurance against cycles and infinite recursion.
Materialised vs not. In Postgres 12+, force materialisation with WITH x AS MATERIALIZED (…) or prevent it with AS NOT MATERIALIZED. Sometimes you want the fence (computed once, reused).
Recursive CTE + LIMIT. BigQuery, Snowflake, Postgres all support it — extremely useful for "first 10 rows of a huge tree walk."
Don't reach for recursion on flat data. If your hierarchy is bounded (max 3 levels: country → state → city), just do 3 explicit JOINs — clearer and faster.

The senior-engineer tell: they read a query and think "CTE stack ✓ or WTF-nested-subquery ✗" as a proxy for code review quality.

(e) Quiz + exercise · 10 min

Rewrite a two-level nested subquery of your choice using CTEs. Which is easier to read?
What are the four required parts of a recursive CTE?
Why is UNION ALL preferred over UNION inside a recursive CTE?
What's the difference between Postgres 11 and Postgres 12+ regarding CTE materialisation?
Name a real-world hierarchy (not org chart) that you'd query with a recursive CTE.
Stretch (connects to S039 Window Functions): the "top earner per dept" query you've now seen three ways — correlated subquery (S038), window function (S039), and CTE (this session). Combine window function + CTE to produce "top 3 earners per dept, one row per employee with rank and dept average alongside." Which composition style feels most maintainable to you?

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

What is CTEs & Recursive Queries? (one sentence, no jargon)
When would you use it? (one concrete example)
When would you NOT use it? (one anti-pattern)

What comes next

Next session (S041): Indexes — B-Tree Intuition, When to Add
Previous (S039): Window Functions — the Game-Changer
Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.