CTEs & Recursive Queries

Runtime contract: This review executed the bounded fixtures with Python’s SQLite 3.45.1. Run the complete Python lab against in-memory SQLite. PostgreSQL 16 materialisation and CYCLE behaviour are source-reviewed references, not PostgreSQL execution. Recursive examples are bounded and distinguish node reachability, paths, cycle detection and truncation.

Why this session exists

A CTE names a query stage: WITH orders_per_customer AS (...) SELECT .... It lets a reviewer inspect grain, filters and dependencies without reading nested parentheses inside out. It is not a Python function, a persistent variable or an automatic cache. A concise subquery remains perfectly reasonable; line count alone does not establish maintainability.

You will refactor subqueries into named stages, compose a window/filter/enrichment pipeline, traverse down and up a hierarchy, generate number/date series, roll up descendants, detect cycles and explain PostgreSQL's version-specific folding rules. Each result has a correctness contract independent of whether the optimiser materialises it.

Prerequisites

Read aggregations and windows. Be able to say what one row of a stage means and whether its grouping/join key is unique.

Named steps, not stored tables

For the employee fixture from aggregation, compare these equivalent result definitions:

SELECT dept,avg_sal FROM (
    SELECT dept,AVG(salary) AS avg_sal
    FROM employees WHERE hired > '2020-01-01' GROUP BY dept
) recent
WHERE avg_sal > 90000 ORDER BY avg_sal DESC,dept;
 
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,dept;

Both return Eng,107500 on that seven-row fixture: Alice and Bob are the qualifying engineers; Dan was hired earlier. HR's remaining Grace has no observed salary, so its average does not pass the filter. This example uses a different employee schema from the management lab below, which has manager IDs and different salaries, not departments.

A CTE has single-statement scope. To inspect an intermediate, retain its needed predecessor definitions and replace the final SELECT with SELECT ... FROM that_stage; it is not available to a later independent statement. Name stages by meaning/grain, such as daily_revenue rather than step2. One coherent responsibility is useful; splitting every SQL operator into its own CTE can obscure rather than clarify intent.

Two original query-writing answers, fully specified

For the exact unfiltered department-average question, the CTE rewrite is:

WITH dept_avg AS (
    SELECT dept,AVG(salary) AS a FROM employees GROUP BY dept
)
SELECT dept,a FROM dept_avg WHERE a>100000 ORDER BY dept;

On the aggregation session's seven-person Grace fixture, Eng=118333.333… is the sole result. Do not accidentally reuse the recent-hires predicate or the 90000 threshold from the separate example above.

To show the top three employees per department alongside the whole eligible department mean:

WITH ranked AS (
    SELECT id,name,dept,salary,
           ROW_NUMBER() OVER (PARTITION BY dept
             ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END,salary DESC,id) AS rn,
           AVG(salary) OVER (PARTITION BY dept) AS dept_avg
    FROM employees
)
SELECT name,dept,salary,dept_avg FROM ranked WHERE rn<=3 ORDER BY dept,rn;

This returns all seven Grace-fixture employees because no department has more than three people, including Grace with NULL salary and HR's observed mean 80000. A fourth engineer would be excluded from display but still contribute to Eng's mean if their salary is known. Computing AVG only after the top-three filter would answer a different question.

Analytics chain: active users, revenue and rank

This PostgreSQL 16 reference uses DATE columns and a fixed report date for reproducibility; current_date - 30 is not portable SQLite date arithmetic.

WITH active_users AS (
    SELECT user_id FROM events
    WHERE event_date >= DATE '2026-01-01' AND event_date < DATE '2026-01-31'
    GROUP BY user_id
), user_revenue AS (
    SELECT user_id,SUM(amount) AS rev FROM orders
    WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2026-01-31'
    GROUP BY user_id
), scored AS (
    SELECT a.user_id,COALESCE(r.rev,0) AS revenue,
           NTILE(10) OVER (ORDER BY COALESCE(r.rev,0) DESC,a.user_id) AS bucket
    FROM active_users a LEFT JOIN user_revenue r USING(user_id)
)
SELECT user_id,revenue FROM scored WHERE bucket=1 ORDER BY revenue DESC,user_id;

Here active nonbuyers are intentionally included with zero revenue. An INNER JOIN would define a different eligible population. “Top decile” from NTILE means row buckets, not a precise 90th-percentile revenue threshold, and fewer than ten users leave some bucket numbers unused. For active users 1,2,3 with revenues 100,50,0, bucket 1 contains user 1; it is one of three users, not exactly 10%. Extract → aggregate → enrich/rank → final filter gives every stage an inspectable population and grain.

The dbt style guide recommends CTE organisation; it does not require every model to be CTE-only. dbt model dependencies use ref()/source() declarations, not merely parsing WITH names into a global lineage graph.

PostgreSQL materialisation: version and conditions

PostgreSQL 12 introduced automatic inlining/folding for eligible CTEs, not unconditional inlining of every nonrecursive query. In PostgreSQL 16 a nonrecursive, side-effect-free SELECT CTE (no volatile functions) is normally folded when referenced once. Multiple references normally favour separate calculation. AS MATERIALIZED forces a boundary; AS NOT MATERIALIZED requests folding for eligible cases and can duplicate work. It is ignored for recursive or non-side-effect-free queries.

The useful performance contrast is an outer selective predicate blocked by a fence:

-- PostgreSQL 16 reference: query existing sandbox big_table(key, payload).
EXPLAIN
WITH w AS MATERIALIZED (SELECT key,payload FROM big_table)
SELECT payload FROM w WHERE key=123;
 
EXPLAIN
WITH w AS NOT MATERIALIZED (SELECT key,payload FROM big_table)
SELECT payload FROM w WHERE key=123;

A filter already inside the CTE still applies there; the old story about a one-row filtered CTE necessarily materialising an entire huge table was misleading. Conversely, materialising a costly expression reused twice can avoid repeated computation. Several CTE Scan nodes may read one computed CTE; their count is not the number of times the underlying source was calculated. Inspect producer nodes, loops and row estimates. EXPLAIN ANALYZE actually executes and should be reserved for an approved safe context.

Redshift's WITH documentation says repeated WITH subqueries may be optimised as common subexpressions. It is not valid to infer “Redshift always fences forever” from its PostgreSQL ancestry. Engine versions and implementations must be checked independently.

SQLite hints and recursive column types are separate contracts

SQLite 3.35+ accepts MATERIALIZED / NOT MATERIALIZED, but its documentation describes planner hints: NOT MATERIALIZED does not prohibit a materialised implementation. Do not transfer PostgreSQL's eligible-folding contract to SQLite just because the spellings match. The local fixtures test results, not a promised count of evaluations of a volatile expression.

A recursive relation must also have compatible columns. PostgreSQL resolves UNION types; an integer anchor with a numeric recursive result can require an explicit cast of the anchor. MySQL 8.0 determines recursive CTE column types from the nonrecursive member: a short string seed may be too narrow for an accumulated path, so cast it wide enough for the declared bounded path. SQLite's dynamic typing does not prove either engine's type acceptance. See MySQL recursive CTE examples, SQLite materialisation hints and PostgreSQL UNION type resolution.

CTE, subquery, temporary table or materialised view?

A materialised CTE is not an independently indexable temporary table with newly gathered table statistics. Underlying table permissions still apply to CTEs. Splitting one statement into several temporary-table statements can change which snapshots are read; readability does not establish transactional equivalence.

Recursive traversal: anchor, frontier and accumulated result

A recursive CTE has a nonrecursive anchor, a UNION or UNION ALL, and a recursive member. PostgreSQL uses the previous iteration's working rows to evaluate the recursive member. Iteration stops when the next working set is empty. SQL text is recursive; execution is iterative. SQLite describes a queue-based implementation. Neither description permits assuming a guaranteed display order; supply outer ORDER BY.

Use the management tree:

Accumulated output has seven people. A path column builds Satya > Rajesh > Alice > Dan; reversing the join follows Dan → Alice → Rajesh → Satya. A path orders ancestry, not necessarily every possible presentation traversal.

UNION ALL retains every generated row, useful for enumerating distinct paths or a verified tree. UNION removes duplicate complete rows, including already produced rows. It can terminate finite reachability even on a cycle when the row is only a node ID. If the row includes increasing depth or path text, every traversal can be a new distinct row and UNION no longer stops the cycle. The original claim that UNION ALL is required was false.

Cycle semantics and resource bounds

A depth cap WHERE depth < 20 admits depth 20 and then stops expansion. It is a resource guard, not proof of a complete hierarchy: a legitimate depth-21 child is omitted. Nor does it bound width; a dense acyclic graph can enumerate exponentially many simple paths.

Choose the task:

• Reachable node set: retain only node identity in a UNION recursive relation; each finite node is admitted once.
• Simple paths / hierarchy diagnostics: carry a visited path and stop extending when a node repeats. Flag the closing cycle row for investigation.
• Bounded-depth query: explicitly declare the depth limit and flag truncation if unvisited children remain at the frontier.

For integer IDs, store delimiters around each ID so ,1, is not mistaken for ,11,. For arbitrary textual IDs use an unambiguous encoding or native arrays rather than this small-model trick. PostgreSQL 16 provides CYCLE ... SET ... USING ...; the Python lab uses SQLite string paths. A statement/instruction budget independently prevents runaway work.

Hands-on: complete safe hierarchy lab

Save as cte_lab.py. It contains staged salary-band enrichment, down/up traversal, number/calendar generation, per-root rollups, category paths, a real cycle test, and truncation diagnostics. The fixture is synthetic; its names do not assert real reporting relationships.

"""Safe CTE chains, reachability, paths and explicit incomplete outcomes."""
import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys=ON")
# Independent work budget; a depth cap alone does not bound graph width.
budget = [0]
def stop_runaway():
    budget[0] += 1
    return budget[0] > 10000
conn.set_progress_handler(stop_runaway, 1000)
conn.executescript("""
CREATE TABLE employees(id INTEGER PRIMARY KEY,name TEXT NOT NULL,
 manager_id INTEGER REFERENCES employees(id),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');
CREATE TABLE categories(id INTEGER PRIMARY KEY,name TEXT NOT NULL,
 parent_id INTEGER REFERENCES categories(id));
INSERT INTO categories VALUES
 (1,'Electronics',NULL),(2,'Phones',1),(3,'Smartphones',2),(4,'Feature Phones',2),
 (5,'Laptops',1),(6,'Gaming Laptops',5),(7,'Books',NULL),(8,'Fiction',7);
""")
 
def run(label, sql, params=()):
    rows = conn.execute(sql, params).fetchall()
    print(label, rows)
    return rows
 
recent = run("Recent direct-report means", """
WITH recent AS (
 SELECT manager_id,AVG(salary) AS avg_sal FROM employees
 WHERE hired>'2019-01-01' GROUP BY manager_id
)
SELECT e.name,r.avg_sal FROM recent r
LEFT JOIN employees e ON e.id=r.manager_id
WHERE r.avg_sal>400000 ORDER BY r.manager_id
""")
assert recent == [('Rajesh',525000.0),('Priya',550000.0)]
run("Three-stage enrichment", """
WITH banded AS (
 SELECT id,name,salary,manager_id,NTILE(3) OVER(ORDER BY salary DESC,id) AS band
 FROM employees
), top_band AS (SELECT * FROM banded WHERE band=1), enriched AS (
 SELECT t.id,t.name,t.salary,m.name AS manager FROM top_band t
 LEFT JOIN employees m ON m.id=t.manager_id
)
SELECT name,salary,manager FROM enriched ORDER BY salary DESC,id
""")
run("Downward paths", """
WITH RECURSIVE reports(id,name,manager_id,depth,path,visited) AS (
 SELECT id,name,manager_id,0,name,','||id||',' FROM employees WHERE id=1
 UNION ALL
 SELECT e.id,e.name,e.manager_id,r.depth+1,r.path||' > '||e.name,
        r.visited||e.id||','
 FROM reports r JOIN employees e ON e.manager_id=r.id
 WHERE r.depth<20 AND instr(r.visited,','||e.id||',')=0
)
SELECT depth,name,path FROM reports ORDER BY depth,id
""")
up = run("Upward chain", """
WITH RECURSIVE chain(id,name,manager_id,depth,visited) AS (
 SELECT id,name,manager_id,0,','||id||',' FROM employees WHERE id=7
 UNION ALL
 SELECT e.id,e.name,e.manager_id,c.depth+1,c.visited||e.id||','
 FROM chain c JOIN employees e ON e.id=c.manager_id
 WHERE c.depth<20 AND instr(c.visited,','||e.id||',')=0
)
SELECT depth,name FROM chain ORDER BY depth
""")
assert up == [(0,'Dan'),(1,'Alice'),(2,'Rajesh'),(3,'Satya')]
run("Squares", """
WITH RECURSIVE nums(n) AS (
 SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n<10
) SELECT n,n*n FROM nums ORDER BY n
""")
calendar = run("January calendar", """
WITH RECURSIVE cal(d) AS (
 SELECT date('2026-01-01') UNION ALL
 SELECT date(d,'+1 day') FROM cal WHERE date(d,'+1 day')<=date('2026-01-31')
) SELECT d FROM cal ORDER BY d
""")
assert len(calendar)==31 and calendar[-1]==('2026-01-31',)
rollup = run("Unique descendants excluding self", """
WITH RECURSIVE tree(root,id) AS (
 SELECT id,id FROM employees UNION
 SELECT t.root,e.id FROM tree t JOIN employees e ON e.manager_id=t.id
)
SELECT r.name,COUNT(*)-1 AS reports_count FROM tree t
JOIN employees r ON r.id=t.root GROUP BY t.root,r.name
HAVING COUNT(*)>1 ORDER BY reports_count DESC,t.root
""")
assert rollup == [('Satya',6),('Rajesh',3),('Priya',1),('Alice',1)]
run("Category breadcrumbs", """
WITH RECURSIVE paths(id,path,depth,visited) AS (
 SELECT id,name,0,','||id||',' FROM categories WHERE parent_id IS NULL
 UNION ALL
 SELECT c.id,p.path||' / '||c.name,p.depth+1,p.visited||c.id||','
 FROM paths p JOIN categories c ON c.parent_id=p.id
 WHERE p.depth<20 AND instr(p.visited,','||c.id||',')=0
)
SELECT depth,path FROM paths ORDER BY path,id
""")
# Real cycle plus 1/11 IDs: delimiters avoid substring false positives.
conn.executescript("""
CREATE TABLE edges(src INTEGER NOT NULL,dst INTEGER NOT NULL,PRIMARY KEY(src,dst));
INSERT INTO edges VALUES(1,2),(2,3),(3,1),(1,11);
""")
walk_sql = """
WITH RECURSIVE walk(id,depth,visited,cycle) AS (
 SELECT 1,0,',1,',0 UNION ALL
 SELECT e.dst,w.depth+1,w.visited||e.dst||',',
        instr(w.visited,','||e.dst||',')>0
 FROM walk w JOIN edges e ON e.src=w.id
 WHERE w.cycle=0 AND w.depth<:cap
)
SELECT id,depth,visited,cycle,
 CASE WHEN cycle=0 AND depth=:cap AND EXISTS(
   SELECT 1 FROM edges e WHERE e.src=walk.id
     AND instr(walk.visited,','||e.dst||',')=0
 ) THEN 1 ELSE 0 END AS truncated
FROM walk ORDER BY depth,id,visited
"""
walk = run("Flag the closing cycle", walk_sql, {'cap':20})
assert [r for r in walk if r[3]] == [(1,3,',1,2,3,1,',1,0)]
assert any(r[0]==11 and r[3]==0 for r in walk)
capped = run("Report cap truncation", walk_sql, {'cap':1})
assert [r[0] for r in capped if r[4]] == [2]
reachable = run("Finite UNION reachability", """
WITH RECURSIVE reach(id) AS (
 SELECT 1 UNION SELECT e.dst FROM reach r JOIN edges e ON e.src=r.id
) SELECT id FROM reach ORDER BY id
""")
assert reachable == [(1,),(2,),(3,),(11,)]
conn.close()
print('CTE checks passed')

Worked lab results

• Recent direct reports hired after 2019-01-01 produce manager Rajesh's mean 525000 and Priya's 550000, both over 400000; Alice's single report Dan averages 180000 and is excluded.
• NTILE(3) over seven salaries puts Satya, Rajesh, Priya in the first three-row band. Enrichment gives Satya no manager and the other two Satya. Ties use ID to keep bucket assignment reproducible.
• Downward traversal has depths 0;1,1;2,2,2;3. Dan's path is Satya > Rajesh > Alice > Dan. Upward traversal starts Dan=0, Alice=1, Rajesh=2, Satya=3.
• Unique reachable descendants per root, excluding the root itself: Satya 6, Rajesh 3, Priya 1, Alice 1; leaves have 0.
• Calendar output contains 31 dates, January 1–31. Numbers 1–10 have squares 1,4,9,16,25,36,49,64,81,100.
• The cycle fixture 1→2→3→1 produces a closing row flagged cycle; it is not expanded again. The legitimate 1→11 branch tests delimiter correctness. A depth-1 cap flags node 2 as truncated because its child 3 remains beyond the boundary.

A root-start traversal alone cannot detect a disconnected cycle with no NULL-parent root. To audit an entire dataset, also compare reached IDs with all IDs, start diagnostics from every component or compute the full relation appropriate to the graph. An FK prevents missing parents, not cycles.

What the cap flag does not certify

In walk_sql, truncated=1 means an unvisited simple-path child remains beyond the cap. It is not a general certificate that every outgoing edge was checked for a cycle. With cap=2 in 1→2→3→1, node 3 is at the frontier and its only child is already visited: truncated=0, but the cycle-closing row at depth 3 was never emitted. A caller requiring complete cycle diagnostics must also report a diagnostic frontier whenever a noncycle row at the cap has any outgoing edge, or run an independently budgeted complete cycle audit. Never interpret zero truncated flags as proof of acyclicity. Cap=20 on the bounded fixture reaches the closing row and proves only that fixture's detection result.

For a diamond 1→2→4 and 1→3→4, node-only UNION emits 1,2,3,4 once each; UNION ALL path enumeration reaches node 4 twice. That multiplicity is correct for paths and wrong for a unique-descendant count. A foreign key establishes existence of a referenced parent, not either graph property.

Category rollup exercise and answer

For categories Electronics→Phones→Smartphones/Feature Phones and Electronics→Laptops→Gaming Laptops, plus Books→Fiction, add six products: two smartphones, one feature phone, one laptop, one gaming laptop and one fiction book.

CREATE TABLE products(id INTEGER PRIMARY KEY,name TEXT,category_id INTEGER REFERENCES categories(id));
INSERT INTO products VALUES
 (1,'iPhone 15',3),(2,'Pixel 8',3),(3,'Nokia 3310',4),
 (4,'ThinkPad X1',5),(5,'ROG Zephyrus',6),(6,'Dune',8);
WITH RECURSIVE descendants(root,descendant) AS (
    SELECT id,id FROM categories
    UNION
    SELECT d.root,c.id FROM descendants d JOIN categories c ON c.parent_id=d.descendant
)
SELECT c.id,c.name,COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN descendants d ON d.root=c.id
LEFT JOIN products p ON p.category_id=d.descendant
GROUP BY c.id,c.name ORDER BY c.id;

Answer: Electronics=5, Phones=3, Smartphones=2, Feature Phones=1, Laptops=2, Gaming Laptops=1, Books=1, Fiction=1. The (root,descendant) UNION deduplicates reachability; adding depth would change its termination contract. A bill-of-materials quantity rollup may need to count several paths and multiply edge quantities instead—unique reachability is not the right replacement for every recursive aggregate.

Number/date generation and boundary exercise

SQLite date arithmetic is not universal SQL. For all Mondays in 2026, test the next generated date so the successor does not cross into 2027:

WITH RECURSIVE mondays(d) AS (
    SELECT date('2026-01-05')
    UNION ALL
    SELECT date(d,'+7 days') FROM mondays
    WHERE date(d,'+7 days') <= date('2026-12-31')
)
SELECT d FROM mondays ORDER BY d;

Answer: 52 dates, first January 5, last December 28. The old WHERE d < '2026-12-31' allowed January 4, 2027 because December 28 passed the test before incrementing.

PostgreSQL reference, with type choices explicit:

SELECT d::date
FROM generate_series(TIMESTAMP '2026-01-05',TIMESTAMP '2026-12-31',INTERVAL '7 days') AS g(d)
ORDER BY d;

A generated daily calendar can LEFT JOIN daily sales to represent known zero-activity days. Do not call a zero-fill complete until source completeness and timezone/grain policies are known.

Wiki ancestor path stretch

A query that takes the deepest row is not necessarily a root path: it may have hit a cycle, missing parent or depth cap. This SQLite reference assumes pages(id INTEGER PRIMARY KEY,parent_id INTEGER) and a bound target parameter:

WITH RECURSIVE chain(id,parent_id,ids,visited,depth,cycle) AS (
    SELECT id,parent_id,CAST(id AS TEXT),','||id||',',0,0
    FROM pages WHERE id=:target_id
    UNION ALL
    SELECT p.id,p.parent_id,CAST(p.id AS TEXT)||'/'||c.ids,
           c.visited||p.id||',',c.depth+1,
           instr(c.visited,','||p.id||',')>0
    FROM chain c JOIN pages p ON p.id=c.parent_id
    WHERE c.cycle=0 AND c.depth<20
)
SELECT ids,
       CASE WHEN cycle=1 THEN 'cycle'
            WHEN parent_id IS NULL THEN 'complete'
            WHEN NOT EXISTS(SELECT 1 FROM pages p WHERE p.id=chain.parent_id) THEN 'missing-parent'
            ELSE 'depth-limit' END AS path_status
FROM chain
ORDER BY depth DESC LIMIT 1;

For pages (1,NULL),(2,1),(3,2) and target 3, answer is 1/2/3, complete. A target absent from pages yields no row; the caller must report not found. Do not grant inherited permissions using a truncated or cyclic path. Folder trees, category trees, dependencies and permission ancestry all fit traversal patterns, but cycles do not automatically require a SAT solver, and object-store key prefixes are not intrinsically a relational folder tree.

Production review and recall answers

1. CTE rewrite? Name the grouped department relation, then filter its average outside; this changes structure, not intended results.
2. Recursive anatomy? Named recursive relation, anchor, UNION/UNION ALL and recursive member. The UNION choice is semantic, not a blanket performance rule.
3. PostgreSQL 11 versus 12? Eligible inlining arrived in 12; single versus multiple references and volatility still matter.
4. A hanging walk? Check cycles, branching/path explosion, joins and resource budgets. A depth guard alone gives a bounded possibly incomplete answer; visited-state or deduplicated reachability supplies the intended semantics.
5. Top three earners plus mean? Compute ROW_NUMBER and AVG OVER in a CTE, then WHERE rn<=3. Put a unique ID in ROW_NUMBER order; the mean must be calculated over the intended population before filtering.
6. Why names help? Each stage exposes its input/output grain and can be inspected with a replacement final SELECT. CTEs do not themselves guarantee one computation or fresh statistics.
7. Performance incident mechanism? A materialisation boundary can prevent an outer filter reaching the base table. Measure that plan, not an unsupported 30× story.
8. Cycle A→B→C→A with depth cap? Repeated nodes up to the cap are not a valid complete hierarchy. Preserve cycle diagnostics and reject that interpretation.

Plan production recursion with row/work budgets and explicit cycle/truncation outcomes. Parameterise the root/target. Compare before/after results and plans when refactoring a hot query. No vendor history or fixed speed ratio replaces these checks.

Explain-out-loud answer guide

1. What is a CTE? A name for a relation within one SQL statement; it is neither persistent storage nor an automatic cache.

2. Why chain named stages? Give each meaningful grain/population a name, retain dependencies, and replace the final SELECT to inspect that stage. Names help review but do not guarantee a faster plan or require one SQL operator per CTE.

3. When WITH RECURSIVE? Follow hierarchy edges, compute reachable nodes or bounded paths, or generate a series. Choose UNION versus UNION ALL and define cycles, resource limits and incomplete outcomes before interpreting the result.

Sources and next steps

Continue to indexes and query planning. CTE producers/scans, intermediate materialisation, data lineage and graph modelling require different evidence; no shared graph or schema file is changed by this lesson.

Primary sources inspected 10 October 2026: PostgreSQL 16 WITH, PostgreSQL 12 release, SQLite WITH, dbt SQL style and Redshift WITH. Exact SQLite results and cycle oracles are saved separately. PostgreSQL plans, CYCLE syntax and generate_series are documentation-reviewed only.

Optional archived resources

These original resource destinations are retained for continuity, not as independently verified technical evidence. Historical video labels, attribution, durations and playback were not verified in this review; search links are discovery queries, not specific lectures. Repeated links are listed once. Prefer the versioned primary sources above for dialect contracts; the archived current-version documentation may evolve.

• Common Table Expressions (CTEs) Explained
• Advanced SQL Tutorial | CTE (Common Table Expression)
• dbt Model Style — CTEs top to bottom
• WITH Queries (Common Table Expressions)
• SQL CTEs — Interactive Tutorial
• How dbt styles SQL — CTE-first