Question 1

What does WITH actually filter?

Accepted Answer

The CTE 'totals' computes the sum per customer: Alice=400, Bob=200. The outer query then filters to totals > 200 - only Alice (400) qualifies. CTEs (Common Table Expressions) are defined with WITH and can be referenced like a table in the subsequent query. They improve readability by breaking complex logic into named steps.

Question 2

When a subquery makes you repeat yourself

Accepted Answer

The main readability and reuse benefit of CTEs: once defined, a CTE can be referenced by name anywhere in the query that follows - including multiple times. A derived table (subquery in FROM) must be duplicated every time you need it. CTEs do not guarantee caching or faster execution - the optimizer may inline them the same way it inlines subqueries.

Question 3

Which engineer makes the cut?

Accepted Answer

Multiple CTEs are separated by commas after WITH. The second CTE 'top_eng' can reference the first CTE 'eng'. 'eng' filters to Engineering employees (Alice and Bob). 'top_eng' keeps only those with the max salary in 'eng' - MAX is 90000, which is Alice. This chaining of CTEs is how you build complex logic in readable steps.

Question 4

Can a later CTE see an earlier one?

Accepted Answer

CTEs in a WITH clause are processed in order, so each one can build on the ones before it. This is what makes chained CTEs so powerful for step-by-step data transformations - you can filter in cte_a, then aggregate in cte_b, then rank in cte_c, each step referring to the previous. The only restriction is that a non-recursive CTE cannot reference itself. Forward references (cte_a referencing cte_b defined later) are not allowed.

Question 5

How long does a CTE live?

Accepted Answer

A CTE's lifetime is exactly one statement. Once the query it belongs to finishes executing, the CTE is gone. If you need to reuse the result across multiple queries or statements, use a temporary table (CREATE TEMP TABLE) or a view (CREATE VIEW) instead. This scoping rule is what distinguishes CTEs from views and temp tables.

Question 6

Aggregating before you join

Accepted Answer

The CTE aggregates orders per customer: customer_id 1 (Alice) = 300, customer_id 2 (Bob) = 50. The outer query joins customers to the CTE result by id. Both customers match, so both appear in the output. Using a CTE here avoids embedding the GROUP BY logic directly inside the JOIN, keeping the query easier to read.

Question 7

When you need the same result in two places

Accepted Answer

The name is the key advantage: write WITH recent_orders AS (...) once, then reference recent_orders in both the main SELECT and a JOIN. With subqueries you would embed the same SELECT twice. Whether the CTE actually executes once or twice depends on the optimizer - PostgreSQL inlines non-recursive CTEs by default since version 12, so materialization is not guaranteed. The readability benefit is real regardless of execution strategy.

Question 8

What does WITH (CTE) do?

Accepted Answer

A CTE is syntactic sugar - the database runs the CTE's SELECT and lets you reference the result by name in the main query. CTEs do not persist after the query ends and they are not automatically cached. Their main benefit is readability: you can name an intermediate step instead of nesting subqueries three levels deep.

Question 9

What makes a CTE recurse?

Accepted Answer

A recursive CTE has two parts joined by UNION ALL: the anchor member (base case, runs once) and the recursive member (references the CTE itself, runs repeatedly until no new rows are produced). The classic use cases are: walking an employee-manager hierarchy to find all reports at any depth, traversing a tree of categories, or generating a sequence of numbers. Most databases (PostgreSQL, SQL Server, SQLite) support recursive CTEs; MySQL added support in version 8.0.

Question 10

Where does the recursion stop?

Accepted Answer

The anchor SELECT 1 produces the first row: n=1. The recursive member adds 1 each iteration while n < 4. Iteration 1: n=1 -> n+1=2. Iteration 2: n=2 -> 3. Iteration 3: n=3 -> 4. Iteration 4: n=4 fails WHERE n < 4, so recursion stops. The final result is 1, 2, 3, 4. This technique generates sequences without needing a numbers table.

Question 11

CTEs and the optimizer

Accepted Answer

Before PostgreSQL 12, all CTEs were 'optimization fences' - the planner always materialized them and could not push predicates into them. From PostgreSQL 12 onward, the planner inlines non-recursive CTEs by default, allowing predicate pushdown and other optimizations. To force materialization (which can be useful if the CTE is expensive and referenced multiple times), use WITH cte AS MATERIALIZED (...). This change surprised developers who relied on the old fence behavior to avoid re-evaluation.

Question 12

Five queries, one expensive aggregation

Accepted Answer

A CTE lives only for the single statement it is attached to. If you need the result across multiple queries, you must recreate the CTE each time - potentially running the expensive aggregation repeatedly. A temp table (CREATE TEMP TABLE ... AS SELECT ...) materializes the result once and is accessible for the rest of the session. You can even index it. Use CTEs for readability within one query; use temp tables when you need persistence or want to guarantee a single execution.

Question 13

Walking a reporting chain with recursion

Accepted Answer

The anchor selects Alice (manager_id IS NULL) at depth 0. The recursive member joins employees back to org, adding depth+1 for each level. Pass 1: Bob reports to Alice (depth 0+1=1). Pass 2: Carol reports to Bob (depth 1+1=2). No one reports to Carol, so recursion stops. This pattern works for any hierarchy - product categories, file systems, org charts - regardless of depth.

Question 14

Guaranteeing a CTE runs only once

Accepted Answer

PostgreSQL 12+ inlines non-recursive CTEs by default, which means the planner might execute the CTE body twice when referenced twice. Adding the MATERIALIZED keyword opts back into the old fence behavior: the result is computed once, stored in memory, and reused. The trade-off is that materialization prevents predicate pushdown from the outer query into the CTE. Use MATERIALIZED when the CTE is expensive and reuse matters more than optimal filtering.

Question 15

What makes a CTE recursive?

Accepted Answer

A recursive CTE has two parts separated by UNION ALL: the anchor (base case) that runs once to seed the result, and the recursive member that joins back to the CTE itself to extend it. The database keeps running the recursive member until it produces no new rows. This pattern is how you traverse hierarchical data like org charts or bill-of-materials trees without knowing the depth in advance.