Module 3 — CTEs (Common Table Expressions)

Why CTEs exist

In Module 2 you wrote queries with subqueries nested inside WHERE or SELECT. That works, but once you need three or four layers of "first compute this, then compute that from it," nested subqueries turn into an unreadable pyramid — brackets inside brackets inside brackets, read from the inside out.

A CTE (WITH name AS (...)) lets you give a subquery a name and write it before the query that uses it, top to bottom, like a recipe: "first do this, call it X. Then do that using X, call it Y. Finally, use Y here." It's the same execution idea as a subquery — but readable in the order a human thinks.

What a CTE actually is

A CTE is a named, temporary result set that exists only for the duration of one SQL statement. It is defined with the WITH keyword, given a name, and can then be referenced by that name later in the same statement — in the main query, or even by another CTE defined after it.

When to use it

• When a query needs multiple logical steps (filter → aggregate → rank → filter again) and nesting subqueries would hurt readability.
• When the same derived result is needed more than once in a query (referencing a CTE by name twice is cleaner than duplicating a subquery twice — though not always cheaper, see Internals below).
• When you want a query that reads top-to-bottom like a checklist, so teammates (and future you) can follow the logic without rewinding.
• When you need recursion — a RECURSIVE CTE is the *only* standard way to walk a hierarchy in plain SQL (full topic in the Recursive CTEs section, Level 5 below).

When NOT to use it

• For a single, simple, one-line filter — a plain subquery or even a plain WHERE clause is shorter and just as clear.
• When you actually need the result reused across multiple separate statements — that's what a view or a real staging table is for, not a CTE (a CTE dies the moment the statement finishes).
• Blindly, for performance, assuming "CTE = faster/cached" — in most databases a non-recursive CTE is just a readability tool; the engine is free to inline it back into a subquery. Never assume a CTE is materialized unless you check (see Internals).

Schema & sample data for this module

We reuse employees / departments and customers / orders exactly as in Modules 1–2. We add one new table, order_items, so we have a genuine multi-step pipeline (orders → line items → product-level totals) to build chained CTEs on top of.

DDL (recap + new table)

-- employees / departments: identical to Module 1
-- customers / orders: identical to Module 2 (order 5005 has a dangling customer_id = 999)

CREATE TABLE order_items (
    item_id     INT PRIMARY KEY,
    order_id    INT,             -- FK to orders
    product     VARCHAR(50),
    quantity    INT,
    unit_price  DECIMAL(10,2)
);

INSERT INTO order_items VALUES
(9001, 5001, 'Keyboard',  2,  1200.00),
(9002, 5001, 'Mouse',     1,   100.00),
(9003, 5002, 'Monitor',   1,  1200.00),
(9004, 5003, 'Mouse',     2,   400.00),
(9005, 5004, 'Laptop',    1,  4300.00),
(9006, 5005, 'Keyboard',  1,   650.00);  -- belongs to the orphaned order 5005

The basic WITH syntax

What it is: WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name. The part inside the parentheses runs conceptually first (or is folded into the outer query — see Internals), and everywhere you write cte_name afterward, the database substitutes that result.

Easy example

Find employees who earn more than the company-wide average salary — same question as Module 2, rewritten with a CTE.

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM employees
)
SELECT e.emp_name, e.salary
FROM employees e, avg_salary a
WHERE e.salary > a.avg_sal;

Line by line: avg_salary is defined as a named result containing one row, one column (avg_sal). The main query then treats avg_salary exactly like a real table — here it's cross-joined (comma join) with employees, which is safe because avg_salary only ever has exactly one row, so it doesn't multiply anything.

Cleaner version using an explicit JOIN

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT e.emp_name, e.salary, a.avg_sal
FROM employees e
JOIN avg_salary a ON TRUE;   -- always-true join condition since avg_salary has 1 row

CTE vs subquery vs view

These three all wrap a query, but they solve different problems. Confusing them is a very common beginner (and interview) mistake.

Multiple independent CTEs

What it is: you can define more than one CTE in the same WITH clause, separated by commas. Each one is its own named result. They don't have to depend on each other — you can just define two unrelated helper queries and use both in the main query.

Medium example

For each department, show headcount and, separately, the department's total order revenue from customers who happen to share the department's location as their country name (a contrived but realistic-shaped "combine two unrelated aggregates" report).

WITH dept_counts AS (
    SELECT dept_id, COUNT(*) AS headcount
    FROM employees
    GROUP BY dept_id
),
customer_revenue AS (
    SELECT c.country, SUM(o.total_amount) AS revenue
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.country
)
SELECT d.dept_name, dc.headcount, cr.revenue
FROM departments d
LEFT JOIN dept_counts dc ON d.dept_id = dc.dept_id
LEFT JOIN customer_revenue cr ON d.location = cr.country;   -- location vs country rarely match in real data; illustrative only

Line by line: dept_counts and customer_revenue are two completely independent CTEs — neither references the other. Both are simply available, by name, to the final SELECT, exactly as if they were two real tables.

Chained CTEs

What it is: a CTE defined later in the WITH clause can reference an earlier CTE by name. This is where CTEs really shine — each step builds on the previous one, like a pipeline.

Medium example

Build a per-customer revenue report in three clear steps: (1) compute line-item totals, (2) roll them up to order totals, (3) roll those up to customer totals.

WITH item_totals AS (
    SELECT
        order_id,
        SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
),
order_totals AS (
    SELECT
        o.customer_id,
        it.order_id,
        it.line_total
    FROM item_totals it
    JOIN orders o ON o.order_id = it.order_id
),
customer_totals AS (
    SELECT
        customer_id,
        SUM(line_total) AS total_revenue
    FROM order_totals
    GROUP BY customer_id
)
SELECT c.customer_name, ct.total_revenue
FROM customer_totals ct
JOIN customers c ON c.customer_id = ct.customer_id
ORDER BY ct.total_revenue DESC;

Line by line: item_totals only touches order_items. order_totals only references item_totals (not the raw table) plus orders, to attach a customer_id. customer_totals only references order_totals. Each step has exactly one job, and you could test each one individually just by running SELECT * FROM item_totals style checks while building the query — this is the single biggest practical advantage of chaining CTEs over nesting subqueries.

Modular SQL design

Chained CTEs aren't just a syntax trick — they're a design philosophy borrowed from software engineering: break one big, scary problem into small, named, individually-testable steps.

The discipline

• One CTE, one responsibility. If a CTE's SELECT is doing two unrelated things (filtering AND aggregating AND ranking all at once), split it into two CTEs.
• Name CTEs like variables, not like "step1/step2". item_totals, order_totals, customer_totals tell the reader what's inside without opening the query.
• Test each layer independently while building. Comment out everything after a given CTE, add a SELECT * FROM that_cte LIMIT 20, and eyeball it before building the next layer on top.
• Keep the final SELECT thin. By the time you reach the last SELECT, most of the hard logic should already be done — the final step should read almost like plain English.

How CTEs execute internally

This is the single most-tested "gotcha" concept about CTEs in interviews, and it varies by database engine — so understanding the concept matters more than memorizing one engine's behavior.

Two ways an engine can treat a CTE

• Inlining (a.k.a. "merging"): the engine treats the CTE as if you'd pasted its SQL text directly into every place you referenced it — like a macro. If you reference the CTE three times, its query effectively runs three times. The optimizer can then push filters from the outer query down into the CTE's logic, which can actually make it faster than a naive materialized version.
• Materialization: the engine runs the CTE's query exactly once, stores the result in a temporary work area (memory or disk), and every reference afterward just reads from that stored result. Good when the CTE is expensive and referenced multiple times; bad when the CTE is huge and only a tiny filtered slice of it is actually needed by the outer query, because the filter can't be pushed down into it.

Why this matters

-- If this CTE scans a 500-million-row fact table...
WITH big_scan AS (
    SELECT * FROM order_items
)
-- ...and you only need 3 rows here, inlining lets the filter
-- push down into big_scan's scan. Materialization would force
-- a full scan of order_items FIRST, then filter afterward.
SELECT * FROM big_scan WHERE order_id = 5001;

Scope & self-reference rules

• A CTE is only visible within the single statement it's attached to. You cannot reference it from a completely different query afterward — it's gone the moment that statement finishes.
• A CTE can be referenced multiple times within the same main query (e.g., once to compute a total, once to compute a rank) — this is exactly the situation where "does it inline or materialize" matters most, since each reference re-runs the logic unless it's materialized.
• By default, a CTE cannot reference itself — WITH cte AS (SELECT ... FROM cte) is an error, unless you explicitly write WITH RECURSIVE. That's a deliberate safety rail, and the entire subject of the next section, Recursive CTEs.
• A later CTE can reference an earlier one (chaining, as above), but an earlier CTE can never reference a later one — the dependency graph only flows downward, top to bottom.

Recursive CTEs: The Missing Half of CTEs

Everything so far has been a non-recursive CTE — a named step that runs once and feeds the next step. But some problems aren't "steps," they're loops of unknown depth: walk up a management chain until you hit the CEO, walk down a category tree until you hit leaf nodes, generate 1..N rows where you don't know N in advance. Plain SQL has no FOR loop — a recursive CTE is the standard, portable way to express "repeat this query against its own previous output until nothing new comes out."

Why recursion means something different in SQL

In a procedural language, recursion is a function calling itself. In SQL, a recursive CTE isn't a function call at all — it's a query that repeatedly joins its own result set to itself, once per "generation," until a generation comes back empty. The database runs this as an iterative loop internally, not a call stack, which is why there's no stack-overflow risk the way there is in recursive code — the real risk is an infinite result set, not an infinite call stack (more on that in Recursive CTE Safety below).

WITH RECURSIVE syntax anatomy

WITH RECURSIVE cte_name AS (
    -- 1. ANCHOR MEMBER: runs once, produces the starting row(s)
    SELECT ... FROM base_table WHERE ...

    UNION ALL

    -- 2. RECURSIVE MEMBER: references cte_name itself, joins to the
    --    PREVIOUS generation's output to produce the NEXT generation
    SELECT ... FROM base_table b
    JOIN cte_name c ON b.parent_col = c.id_col
)
SELECT * FROM cte_name;

Two mandatory pieces, always combined with UNION / UNION ALL:

• Anchor query — the non-recursive part. It runs exactly once and seeds the recursion with the starting row(s) (e.g. "the one employee with no manager," "the root category"). It must not reference the CTE's own name.
• Recursive query — the part that does reference the CTE's own name, but critically only ever sees the previous generation's rows, not the whole accumulated result so far. Each execution of the recursive member is one "hop" — one level down (or up) the hierarchy.

The engine's actual loop: run the anchor once → call that generation 0 → feed generation 0 into the recursive member → whatever it returns is generation 1 → feed generation 1 back in → get generation 2 → repeat → stop the moment a generation returns zero rows. The final result is every generation's rows, unioned together.

UNION ALL vs UNION in a recursive CTE

UNION ALL is the overwhelmingly standard choice: it keeps every row from every generation, duplicates included, and is what lets you compute a depth/level or a path (below). Plain UNION also de-duplicates between generations as it goes — which sounds safer, but is actually slower (the engine must compare every new row against everything already produced) and can silently hide genuinely-different rows that happen to look identical (e.g. two different employees who both report to the same manager and coincidentally have the same computed columns). Some engines (SQL Server) will also implicitly stop recursing sooner with plain UNION once no new distinct rows appear, which can mask a bug rather than fix one.

The termination condition

A recursive CTE stops automatically the moment one full pass of the recursive member returns zero rows — there's no explicit "stop" keyword. In practice, termination is guaranteed by the shape of your data, not by anything you write: a management chain terminates because eventually you reach someone whose manager_id matches nobody left to join to; a category tree terminates because leaf categories have no children rows to join against. If your data has no natural "dead end" (a cycle, or a self-referencing row), the query will not terminate on its own — see Recursive CTE Safety for how to guard against that explicitly.

Employee-manager hierarchy example

The classic recursive CTE use case: a single table where each row points to its own parent row via a self-referencing foreign key. We add a small dedicated table for this — employees from earlier modules has no manager_id, so we introduce staff to keep the org-chart example self-contained.

CREATE TABLE staff (
    staff_id    INT PRIMARY KEY,
    staff_name  VARCHAR(50),
    manager_id  INT,             -- self-referencing FK to staff.staff_id; NULL = top of chain
    salary      DECIMAL(10,2)
);

INSERT INTO staff VALUES
(1, 'Alice (CEO)',      NULL, 250000),
(2, 'Bob (VP Eng)',     1,    190000),
(3, 'Carol (VP Sales)', 1,    185000),
(4, 'Dave (EM)',        2,    150000),
(5, 'Eve (EM)',         2,    148000),
(6, 'Frank (IC)',       4,    120000),
(7, 'Grace (IC)',       4,    118000),
(8, 'Heidi (AE)',       3,    95000);

Basic walk: everyone under Bob (VP Eng)

WITH RECURSIVE reports AS (
    -- Anchor: Bob himself, generation 0
    SELECT staff_id, staff_name, manager_id, 1 AS depth
    FROM staff
    WHERE staff_name = 'Bob (VP Eng)'

    UNION ALL

    -- Recursive: everyone whose manager_id points at the PREVIOUS generation
    SELECT s.staff_id, s.staff_name, s.manager_id, r.depth + 1
    FROM staff s
    JOIN reports r ON s.manager_id = r.staff_id
)
SELECT staff_id, staff_name, depth
FROM reports
ORDER BY depth, staff_id;

Line by line: generation 0 is just Bob, depth = 1. Generation 1 is every staff row whose manager_id equals Bob's staff_id — that's Dave and Eve, depth = 2. Generation 2 joins staff against that generation — Frank and Grace report to Dave, so they appear with depth = 3; Eve has no direct reports, so she contributes nothing to generation 2. Generation 3 finds nobody reporting to Frank or Grace, returns zero rows, and the recursion stops.

Depth / level column

Notice depth above: carrying r.depth + 1 forward through every recursive step is the standard way to know how many hops deep a row is — essential for indentation in an org-chart UI, for LIMIT-ing how many levels deep to show, or for a safety cap (see Recursive CTE Safety).

Path column

A path column accumulates the full chain of names (or IDs) from the anchor down to the current row — useful for breadcrumbs, for detecting cycles (a name reappearing in its own path), and for sorting a tree so children stay grouped directly under their parent.

WITH RECURSIVE org_chart AS (
    SELECT
        staff_id, staff_name, manager_id,
        1 AS depth,
        CAST(staff_name AS VARCHAR(500)) AS path       -- seed the path with the root
    FROM staff
    WHERE manager_id IS NULL                            -- Alice, the true top of the chain

    UNION ALL

    SELECT
        s.staff_id, s.staff_name, s.manager_id,
        o.depth + 1,
        o.path || ' > ' || s.staff_name                 -- append this row to the parent's path
    FROM staff s
    JOIN org_chart o ON s.manager_id = o.staff_id
)
SELECT staff_name, depth, path
FROM org_chart
ORDER BY path;

Category / tree traversal example

The same anchor + recursive-member pattern applies to any self-referencing tree, not just org charts — product categories, file-system folders, comment threads, org units. Here's a product category tree.

CREATE TABLE categories (
    category_id        INT PRIMARY KEY,
    category_name      VARCHAR(50),
    parent_category_id INT               -- NULL = top-level category
);

INSERT INTO categories VALUES
(1, 'Electronics',    NULL),
(2, 'Computers',       1),
(3, 'Laptops',         2),
(4, 'Gaming Laptops',  3),
(5, 'Peripherals',     2),
(6, 'Keyboards',       5),
(7, 'Mice',            5);

Top-down traversal: everything under "Computers"

WITH RECURSIVE subtree AS (
    SELECT category_id, category_name, parent_category_id, 0 AS depth
    FROM categories
    WHERE category_name = 'Computers'

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_category_id, s.depth + 1
    FROM categories c
    JOIN subtree s ON c.parent_category_id = s.category_id
)
SELECT REPEAT('  ', depth) || category_name AS indented_name, depth
FROM subtree
ORDER BY depth;

Result: Computers (0) → Laptops, Peripherals (1) → Gaming Laptops, Keyboards, Mice (2). The REPEAT(' ', depth) trick turns depth straight into visual indentation — a common way to render a tree in a flat query result.

Bottom-up traversal: full ancestor chain of "Gaming Laptops"

The same technique runs in reverse just by flipping which side of the join is the anchor and swapping which column feeds which.

WITH RECURSIVE ancestors AS (
    SELECT category_id, category_name, parent_category_id, 0 AS depth
    FROM categories
    WHERE category_name = 'Gaming Laptops'

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_category_id, a.depth + 1
    FROM categories c
    JOIN ancestors a ON c.category_id = a.parent_category_id   -- walk UP: match on the parent's id
)
SELECT category_name, depth
FROM ancestors
ORDER BY depth;

Generating number & date series

Recursive CTEs aren't only for hierarchies — the same anchor/recursive pattern generates a sequence of numbers or dates with no source table at all, which is extremely common for filling gaps in reports (e.g. "show every day this month, even days with zero orders").

Number series (1 to 10)

WITH RECURSIVE numbers AS (
    SELECT 1 AS n                     -- anchor: start at 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10   -- recursive: keep going while n < 10
)
SELECT n FROM numbers;

Notice the termination condition lives directly in the recursive member's WHERE clause — once n reaches 10, n + 1 would be 11, but the WHERE n < 10 guard stops that row from ever being produced, so generation 11 (which would try to compute n = 11) never happens.

Date series (every day in March 2026)

WITH RECURSIVE date_series AS (
    SELECT DATE '2026-03-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day' FROM date_series WHERE day < DATE '2026-03-31'
)
SELECT day FROM date_series;

Production use: filling revenue gaps

Combined with a LEFT JOIN, a generated date series turns "days with orders" into "every day, with 0 where there were none" — the single most common real-world use of a series CTE.

WITH RECURSIVE date_series AS (
    SELECT DATE '2026-03-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day' FROM date_series WHERE day < DATE '2026-03-31'
)
SELECT
    ds.day,
    COALESCE(SUM(o.total_amount), 0) AS daily_revenue
FROM date_series ds
LEFT JOIN orders o ON o.order_date = ds.day     -- assumes an order_date column
GROUP BY ds.day
ORDER BY ds.day;

Preventing infinite recursion & cycle detection

Because termination depends entirely on your data reaching a "dead end," a recursive CTE over data with a cycle — row A points to B, B points back to A — will never naturally terminate. This is the single most dangerous failure mode of recursive SQL: an unbounded, runaway query that keeps consuming memory/disk until the engine kills it or the server falls over.

Cycle detection with a path array

The standard, portable technique: carry an array (or delimited string) of every ID visited so far, and stop recursing down any branch that would revisit an ID already in that path.

WITH RECURSIVE reports AS (
    SELECT staff_id, staff_name, manager_id, ARRAY[staff_id] AS visited
    FROM staff
    WHERE staff_name = 'Alice (CEO)'

    UNION ALL

    SELECT s.staff_id, s.staff_name, s.manager_id, r.visited || s.staff_id
    FROM staff s
    JOIN reports r ON s.manager_id = r.staff_id
    WHERE NOT (s.staff_id = ANY(r.visited))     -- stop the moment we'd revisit a node
)
SELECT staff_id, staff_name, visited FROM reports;

Every recursive engine benefits from this pattern even when the data is currently clean — it's cheap insurance, and it turns "the query hangs forever" into "the query finishes and simply excludes the cyclical branch."

SQL Server: MAXRECURSION

SQL Server adds a built-in depth cap as a query hint — a hard backstop even if your cycle-detection logic has a bug: OPTION (MAXRECURSION 100) appended after the final SELECT. The default is 100; MAXRECURSION 0 disables the cap entirely (dangerous — only for known-bounded data). When the cap is hit, SQL Server raises an error rather than silently truncating results, which is exactly the loud-failure behavior you want.

;WITH reports AS (
    SELECT staff_id, staff_name, manager_id, 1 AS depth FROM staff WHERE manager_id IS NULL
    UNION ALL
    SELECT s.staff_id, s.staff_name, s.manager_id, r.depth + 1
    FROM staff s JOIN reports r ON s.manager_id = r.staff_id
)
SELECT * FROM reports
OPTION (MAXRECURSION 50);

PostgreSQL: SEARCH and CYCLE clauses

PostgreSQL 14+ offers standard-SQL syntax that generates the depth/path and cycle-detection bookkeeping for you, instead of hand-rolling the array pattern above.

WITH RECURSIVE reports AS (
    SELECT staff_id, staff_name, manager_id
    FROM staff WHERE manager_id IS NULL
    UNION ALL
    SELECT s.staff_id, s.staff_name, s.manager_id
    FROM staff s JOIN reports r ON s.manager_id = r.staff_id
)
SEARCH DEPTH FIRST BY staff_id SET ordercol     -- adds an ordering column for tree-order output
CYCLE staff_id SET is_cycle USING path          -- adds is_cycle (bool) + path, auto-detected
SELECT * FROM reports;

SEARCH DEPTH FIRST (or BREADTH FIRST) auto-generates an ordering column so results come back in proper tree order without you hand-building a path string just for sorting. CYCLE ... SET is_cycle USING path auto-generates both the cycle-detection array and a boolean flag, replacing the manual ARRAY[...] || ... WHERE NOT (... = ANY(...)) pattern above.

Recursive CTE performance tips

• Index the join column. The recursive member's join (manager_id, parent_category_id) runs once per generation — an index on that FK column matters far more here than in a typical one-shot query, since it's hit repeatedly.
• Filter in the anchor, not after. Scoping the anchor to exactly the subtree you need (WHERE staff_name = 'Bob (VP Eng)') is far cheaper than recursing the entire org chart and filtering the final result.
• Cap depth defensively even on trusted data. Add WHERE depth < 50 in the recursive member as a belt-and-suspenders guard, independent of engine-specific caps like MAXRECURSION.
• Avoid unnecessary columns in the recursive member. Every generation re-materializes its working set; carrying wide, unused columns through every hop multiplies that cost for no benefit.
• Watch out for accidental UNION instead of UNION ALL — beyond correctness, the implicit de-duplication becomes a real bottleneck on deep or wide hierarchies since it compares every new row against the growing accumulated set.

Dialect differences across engines

CTE syntax is standardized enough to be broadly portable, but the details below trip up even experienced engineers moving between engines — especially around recursion, which has the widest divergence.

Oracle's alternative: CONNECT BY PRIOR

Because it predates the SQL standard's recursive CTE syntax, Oracle has its own hierarchy-walking syntax that's still widely used in legacy code:

SELECT staff_id, staff_name, LEVEL AS depth
FROM staff
START WITH manager_id IS NULL
CONNECT BY PRIOR staff_id = manager_id;

START WITH plays the anchor's role; CONNECT BY PRIOR parent_col = child_col plays the recursive member's role; LEVEL is a built-in depth pseudo-column, no manual depth + 1 needed. Oracle also supports standard recursive CTEs today — CONNECT BY mainly matters for reading existing Oracle codebases, not for new code.

CTEs with data modification: WITH ... UPDATE / DELETE / INSERT

Every example so far has been a CTE feeding a SELECT. CTEs can also feed UPDATE, DELETE, and INSERT statements — using a multi-step, readable pipeline to compute exactly which rows to modify, then handing that computed set to the DML statement.

Deduplicate and delete duplicate rows using ROW_NUMBER()

The read-only dedup pattern from the Data Engineering section, extended to actually delete the duplicates rather than just filter them out of a SELECT.

-- PostgreSQL / SQL Server: CTE feeds a DELETE directly
WITH ranked AS (
    SELECT
        item_id,
        ROW_NUMBER() OVER (
            PARTITION BY order_id, product
            ORDER BY item_id
        ) AS rn
    FROM order_items
)
DELETE FROM order_items
WHERE item_id IN (SELECT item_id FROM ranked WHERE rn > 1);

Update records from a computed CTE

Give every order a computed line_total back onto the orders table itself, sourced from a CTE that rolls up order_items — a common "backfill a denormalized column" pattern.

-- PostgreSQL
WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS computed_total
    FROM order_items
    GROUP BY order_id
)
UPDATE orders o
SET total_amount = it.computed_total
FROM item_totals it
WHERE it.order_id = o.order_id;

-- SQL Server (UPDATE ... FROM syntax differs)
WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS computed_total
    FROM order_items
    GROUP BY order_id
)
UPDATE o
SET o.total_amount = it.computed_total
FROM orders o
JOIN item_totals it ON it.order_id = o.order_id;

Insert aggregated results into a reporting table

A CTE computing a rollup, followed by INSERT ... SELECT from that CTE into a separate summary table — the query-time version of a nightly reporting job.

WITH customer_totals AS (
    SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_revenue
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.customer_name
)
INSERT INTO customer_revenue_report (customer_id, customer_name, total_revenue, report_date)
SELECT customer_id, customer_name, total_revenue, CURRENT_DATE
FROM customer_totals;

Why the syntax differs so much by engine

Standard SQL only formally defines CTEs feeding SELECT. Writable-CTE support for UPDATE/DELETE/INSERT is each engine's own extension, added independently — which is exactly why PostgreSQL's UPDATE ... FROM cte, SQL Server's UPDATE alias SET ... FROM table alias JOIN cte, and MySQL's more limited support (MySQL historically restricts updating a table referenced inside its own CTE — check current version support before relying on it) all look different. Snowflake and BigQuery follow patterns closer to Postgres. There is no fully portable writable-CTE syntax — always verify against your specific engine's docs rather than assuming a pattern that worked on one engine ports directly to another.

Staged ETL pipelines with CTEs

Let's fix the silent data-loss bug from the Chained CTEs section, the way a real data engineer would: make every stage's data-quality decision explicit and visible, instead of letting INNER JOINs quietly drop rows.

WITH cleaned_orders AS (
    -- Stage 1: flag orphaned orders instead of silently dropping them
    SELECT
        o.order_id,
        o.customer_id,
        CASE WHEN c.customer_id IS NULL THEN TRUE ELSE FALSE END AS is_orphaned
    FROM orders o
    LEFT JOIN customers c ON c.customer_id = o.customer_id
),
item_totals AS (
    -- Stage 2: line-item rollup, unrelated to the customer-quality question
    SELECT order_id, SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
),
enriched_orders AS (
    -- Stage 3: combine stage 1 + stage 2
    SELECT
        co.order_id,
        co.customer_id,
        co.is_orphaned,
        COALESCE(it.line_total, 0) AS line_total
    FROM cleaned_orders co
    LEFT JOIN item_totals it ON it.order_id = co.order_id
)
SELECT
    order_id,
    customer_id,
    line_total,
    CASE WHEN is_orphaned THEN 'REVIEW: missing customer' ELSE 'OK' END AS data_quality_flag
FROM enriched_orders
ORDER BY is_orphaned DESC;

Order 5005 now appears in the output with an explicit flag, instead of disappearing. This is the difference between a pipeline that fails loudly (good) and one that fails silently (dangerous) — the exact same lesson from the LEFT JOIN production example in Module 1, now applied across a multi-stage pipeline.

Deduplication with a CTE

A CTE combined with ROW_NUMBER() (full detail in Module 6, previewed here since it's such a common CTE use case) is the standard production pattern for removing duplicate rows.

WITH ranked AS (
    SELECT
        item_id, order_id, product, quantity, unit_price,
        ROW_NUMBER() OVER (
            PARTITION BY order_id, product   -- "duplicate" means same order + same product
            ORDER BY item_id                 -- keep the earliest inserted row
        ) AS rn
    FROM order_items
)
SELECT item_id, order_id, product, quantity, unit_price
FROM ranked
WHERE rn = 1;

Why a CTE here specifically: ROW_NUMBER() cannot be referenced directly in the same SELECT's WHERE clause (window functions are computed after WHERE conceptually) — so you must compute it in one layer (the CTE) and filter it in the next. This is one of the few cases where a CTE isn't just "for readability," it's functionally required by SQL's rules of execution order.

When CTEs hurt performance

• Re-computation on every reference. If a CTE is referenced 4 times in the outer query and the engine inlines it, you've effectively written the same expensive subquery 4 times.
• Blocking predicate pushdown when forced-materialized. If an engine (or an explicit MATERIALIZED hint) computes the full CTE before any outer filter is applied, a CTE that scans a huge table but is only needed for 10 rows can become a full-table-scan bottleneck.
• Long chains hiding a bad early step. A mistake in CTE #1 of a 6-CTE chain silently propagates through every later stage — always spot-check the earliest CTEs first when a long pipeline gives wrong output.
• Over-normalizing a simple query into 5 CTEs "for style" — readability is the goal, not CTE count. If two steps are trivially simple and always used together, merging them can be clearer, not worse.

Performance deep dive: CTE vs every alternative

Internals covered how a CTE executes; this section covers when to reach for something else entirely. Four alternatives, and when each wins.

CTE vs temp table

A CTE is recomputed (or re-referenced against an in-memory work area, depending on materialization) within a single statement and vanishes immediately after. A temp table is a real, physical (or memory-backed) table that persists for the session or transaction, can be indexed, and can be queried by later, separate statements. Reach for a temp table over a CTE when: the intermediate result is genuinely large and reused by several subsequent statements, when you need to build an index on the intermediate result to make a later join fast, or when you need to ANALYZE/collect statistics on it so the optimizer can plan later steps well — none of which a CTE supports.

-- Temp table: computed once, indexed, reused by multiple later statements
CREATE TEMP TABLE tmp_customer_totals AS
SELECT customer_id, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY customer_id;

CREATE INDEX ON tmp_customer_totals (customer_id);

-- Now multiple separate queries can use it, and the optimizer has real stats to work with
SELECT * FROM tmp_customer_totals WHERE total_revenue > 1000;
SELECT AVG(total_revenue) FROM tmp_customer_totals;

CTE vs derived table / subquery

Functionally near-identical — a derived table is the same idea as a CTE, just unnamed and written inline in the FROM clause instead of declared up top. Most modern optimizers (Postgres, SQL Server, Snowflake) treat them the same way internally. The choice is almost purely about readability: pick CTEs once you have more than one logical step, since naming each step beats nesting parentheses.

CTE vs view

Already covered in Level 2 — a view is stored, permanent, and reusable across statements; a CTE is neither. Reach for a view when multiple different queries/reports need the same logic; reach for a CTE when the logic is specific to one query.

CTE vs materialized view

A materialized view physically stores its result set on disk and must be explicitly refreshed (on a schedule, on demand, or via triggers depending on engine) — it can go stale between refreshes, but reads from it are as fast as reading any indexed table. Reach for a materialized view when an aggregate is expensive to compute, queried extremely often, and slightly-stale data is acceptable; a CTE can never give you this because it recomputes from scratch every single statement, with zero staleness but zero caching either.

Predicate pushdown

Predicate pushdown is the optimizer moving a WHERE filter from the outer query down into an inlined CTE's own scan, so the filter is applied as early as possible — often turning a full-table scan into an index seek. This only happens when the CTE is inlined (see Internals); a forced-materialized CTE, a temp table, or in some engines a CTE referenced multiple times blocks pushdown entirely, because the full result must be computed before any outer filter can be applied to it.

-- Inlined: optimizer can push order_id = 5001 down into the order_items scan
WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
)
SELECT * FROM item_totals WHERE order_id = 5001;
-- vs. NOT MATERIALIZED / MATERIALIZED (Postgres) forces the engine's hand explicitly either way

Repeated CTE references & when CTEs are recomputed

If a CTE is referenced N times in the outer query and the engine chooses to inline it, its underlying query runs N separate times — once per reference, each potentially against the full base table before any of that reference's own filters apply. If the engine instead materializes it (automatically, or via an explicit hint), it computes once and every reference reads the same stored result. Whether a given engine defaults to inlining or materializing a multiply-referenced CTE is engine- and version-specific — never assume, always check EXPLAIN.

When to use temp tables for large intermediate results

• The intermediate result is scanned by 3+ downstream statements, not just referenced 3+ times within one statement.
• You need an index on the intermediate result specifically to make a later join or filter fast.
• The intermediate computation is genuinely expensive (large aggregation, big join) and you want a hard guarantee it only runs once, independent of what the optimizer decides to do with a CTE.
• You need the optimizer to have real row-count/statistics information about the intermediate result for planning later steps — a CTE typically doesn't get its own collected statistics the way a materialized temp table does.

How to read EXPLAIN / EXPLAIN ANALYZE

EXPLAIN shows the planned execution strategy without running the query; EXPLAIN ANALYZE actually runs it and reports real timings and row counts alongside the plan — always prefer ANALYZE when diagnosing a real slow query, since the planner's estimates can be wrong.

EXPLAIN ANALYZE
WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
)
SELECT * FROM item_totals WHERE order_id = 5001;

• Look for a node explicitly labeled CTE Scan (Postgres) — its presence means that CTE was materialized; its absence means the CTE's logic was folded directly into the surrounding plan (inlined).
• Compare the planner's estimated rows against actual rows (shown by ANALYZE) — a huge mismatch is a strong sign of stale statistics or a filter that isn't being pushed down the way you expect.
• Check whether an index is actually being used (Index Scan/Index Seek) versus a full Seq Scan/Table Scan on the CTE's underlying base table — a missing or unused index is the single most common cause of a slow chained-CTE query.

Indexing base tables used inside CTEs

A CTE has no indexes of its own — every reference re-scans the underlying base table(s) it's built from (unless materialized into a temp-table-like structure the engine happens to index internally, which you generally can't rely on). This means the single highest-leverage performance fix for a slow CTE-heavy query is almost always an index on the base table's join/filter columns, not restructuring the CTEs themselves.

-- If every CTE in a pipeline filters/joins on order_id, THIS is the fix,
-- not rewriting the CTEs:
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

Production patterns for CTE pipelines

Patterns that show up constantly once CTE chains move from "query that answers one question" to "logic a real pipeline depends on."

The four-stage shape: staging → cleaned → enriched → final

The ETL Pipeline section already demonstrated this; naming it explicitly helps you recognize it as a repeatable template rather than a one-off trick: staging (raw source, untouched) → cleaned (type-cast, deduped, nulls handled, bad rows flagged not dropped) → enriched (joined against reference/dimension data) → final (aggregated/shaped exactly for the consumer). Most production CTE chains that feel "too long" are actually fine once you can point at which stage each CTE belongs to.

Data quality checks as CTEs

Encode a data-quality rule as its own named CTE that returns violating rows, and union or report on it explicitly rather than silently filtering bad rows out of the main pipeline.

WITH dq_orphaned_orders AS (
    SELECT order_id, 'orphaned: no matching customer' AS issue
    FROM orders o
    WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id)
),
dq_zero_amount AS (
    SELECT order_id, 'zero or negative total_amount' AS issue
    FROM orders
    WHERE total_amount <= 0
)
SELECT * FROM dq_orphaned_orders
UNION ALL
SELECT * FROM dq_zero_amount;

Reconciliation queries

A reconciliation query compares row counts or sums between two stages (or two systems) that should match, and flags drift — the automated version of the manual "spot-check each CTE" debugging habit.

WITH source_count AS (SELECT COUNT(*) AS n FROM orders),
     staged_count AS (SELECT COUNT(*) AS n FROM enriched_orders)   -- from a prior pipeline stage
SELECT
    s.n AS source_rows,
    t.n AS staged_rows,
    s.n - t.n AS row_drift
FROM source_count s CROSS JOIN staged_count t;

Slowly changing dimension (SCD) helper queries

A CTE isolating "which dimension rows changed since the last load" is the core building block of an SCD Type 2 upsert — compute the diff first, then branch into insert-new-version / expire-old-version logic downstream.

WITH changed_customers AS (
    SELECT s.customer_id, s.customer_name, s.country
    FROM staging_customers s
    JOIN customers c ON c.customer_id = s.customer_id
    WHERE s.customer_name IS DISTINCT FROM c.customer_name    -- Postgres NULL-safe comparison
       OR s.country IS DISTINCT FROM c.country
)
SELECT * FROM changed_customers;

Incremental load validation

Before merging new data into a target table, a CTE that isolates "what would be new/changed by this load" lets you validate row counts and spot-check before committing — catching a bad upstream extract before it corrupts the target.

WITH incoming AS (
    SELECT * FROM staging_orders WHERE load_batch_id = 'batch_2026_03_13'
),
new_rows AS (
    SELECT i.* FROM incoming i
    LEFT JOIN orders o ON o.order_id = i.order_id
    WHERE o.order_id IS NULL
)
SELECT COUNT(*) AS new_row_count FROM new_rows;
-- Sanity-check this number against expected batch size BEFORE running the actual INSERT.

Dedup before merge/upsert

Always deduplicate the incoming/staged side with the ROW_NUMBER() pattern (Data Modification section) before a MERGE/upsert — most engines' MERGE will error or behave unpredictably if the source side contains duplicate keys matching the same target row.

Isolating business logic by grain

Name and structure CTEs around the grain (one row = one what?) they operate at — order_item_level, order_level, customer_level — and never let a single CTE silently mix grains (e.g. joining item-level rows to customer-level rows without an explicit aggregation step in between). Grain mismatches are one of the most common sources of silently-wrong totals (full treatment in Correctness, Level 9).

Naming conventions

• Name by what the CTE contains, not its position: customer_totals, not step3 or cte2.
• Suffix by grain when it helps: _by_customer, _by_order.
• Prefix quality-check CTEs distinctly: dq_ for data-quality, recon_ for reconciliation — makes them instantly recognizable when scanning a long chain.

Avoiding unreadable CTE chains

If you're past 6–8 CTEs in one statement and still adding more, that's usually a signal to "graduate" the earliest, most stable CTEs into real staging tables or dbt models (as noted in the ETL Pipeline section) rather than continuing to grow one giant statement. Long chains aren't inherently wrong, but a chain nobody can hold in their head at once has stopped being more readable than what it replaced.

Testing each CTE layer independently

The habit introduced in Modular SQL Design, formalized: before trusting a multi-stage pipeline, temporarily terminate it after each CTE with a plain SELECT * FROM that_cte LIMIT 20 (or, better, wrap each stage's row count in the reconciliation pattern above) and confirm it looks right before building the next layer on top. Debugging top-down, one verified layer at a time, is dramatically faster than debugging a 6-CTE chain's wrong final output by guessing.

Correctness pitfalls in CTE pipelines

A CTE chain can run without errors and still be silently wrong. These are the specific ways that happens most often in production.

Join type decisions inside CTEs

Every join inside every CTE stage is a decision about which rows are allowed to disappear. The Chained CTEs section already showed this concretely: switching every join to INNER "because it was simplest" silently dropped customer 204 and order 5005. Before writing a join inside a CTE, always ask explicitly: should rows with no match on this side disappear, or should they be kept and flagged?

Row loss from accidental INNER JOIN

The most common single bug in CTE pipelines. An INNER JOIN where a LEFT JOIN was intended silently removes exactly the rows you most need to see — the ones with missing/bad related data. It produces no error, no warning, just a smaller-than-expected result set that looks plausible.

Duplicate multiplication from joining before aggregating

Joining a one-to-many relationship (orders to order_items) before aggregating, when the goal was an order-level total, silently multiplies each order's other columns once per line item — a classic source of wildly inflated totals that still "look" like real numbers.

-- WRONG: joins orders to order_items (1-to-many), then sums total_amount,
-- which gets counted once PER LINE ITEM, not once per order
WITH bad_totals AS (
    SELECT o.customer_id, SUM(o.total_amount) AS revenue   -- multiplied by item count!
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY o.customer_id
)
SELECT * FROM bad_totals;

-- RIGHT: aggregate order_items to order grain FIRST, in its own CTE,
-- before joining anything else onto it
WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
)
SELECT o.customer_id, SUM(it.line_total) AS revenue
FROM orders o
JOIN item_totals it ON it.order_id = o.order_id
GROUP BY o.customer_id;

Grain mismatch between CTE stages

Every CTE has an implicit grain — what one row represents. A pipeline stage bug is very often exactly this: stage 2 assumes stage 1's grain is "one row per order" when stage 1 actually produces "one row per order line item," and every join after that point silently multiplies. Comment each CTE's grain explicitly (-- grain: one row per order) in any pipeline with more than 2–3 stages.

Null handling with COALESCE

A LEFT JOIN that correctly preserves an unmatched row still leaves NULL in every column from the unmatched side — and NULL silently poisons downstream arithmetic (NULL + 5 = NULL, not 5) and comparisons (NULL = NULL is NULL, not TRUE). Wrap any column that might be NULL after a LEFT JOIN in COALESCE(col, default) the moment you intend to use it in math, exactly as the ETL Pipeline section did with COALESCE(it.line_total, 0).

Decimal/rounding issues in totals

Summing many small ROUND()-ed values can produce a total that doesn't match the sum of the unrounded values — round only at the final display layer, never in an intermediate CTE stage that feeds further aggregation. Also watch integer-division truncation (seen in the FAANG percentage-of-total example) — force a decimal/float context explicitly (100.0 *, not 100 *) anywhere division happens inside a CTE chain.

Tie handling in ranking queries

ORDER BY ... LIMIT 1 silently picks one arbitrary winner when there's a tie for first place; RANK()/DENSE_RANK() (Module 6) inside a CTE, filtered to = 1 in the layer above, correctly returns all tied rows. Always ask "could there be a tie, and if so, do we want one row or all of them?" before defaulting to LIMIT 1 — this exact question appeared in the Hard Interview Questions section above.

Maintainability

A CTE chain that's correct today but unreadable is a bug waiting for the next person (often future you) to introduce by accident. These habits, already touched on individually above, form one coherent discipline when applied together.

• One responsibility per CTE. If describing a CTE's job needs the word "and" twice, split it.
• Meaningful CTE names. customer_totals, not t1 — a name should let a reader skip reading the CTE's body entirely and still understand the pipeline.
• Keep the final SELECT simple. By the last step, the hard logic should already be done; the final SELECT should read close to plain English.
• Avoid step1, step2, cte1. Positional names force a reader to hold the entire chain in their head just to know what's in each one.
• Comment only for business rules, not mechanics. -- exclude test accounts per Finance request 2026-02 is a useful comment; -- join orders to customers is noise the SQL already says.
• Avoid huge 15-CTE queries when temp tables or dbt-style models are clearer. Past a certain size, a single statement stops being the right unit of readability — split it into real, separately-testable objects instead (Avoiding Unreadable CTE Chains, above).

Debugging CTE chains

A concrete, repeatable process — the same one demonstrated informally in Hard Question H3 above, written out as a checklist.

1. Run each CTE independently. Temporarily end the statement after the first CTE with SELECT * FROM first_cte LIMIT 20 and eyeball it before trusting anything built on top of it.
2. Compare row counts between stages. Each stage's row count should match your mental model of its grain (same as the source? aggregated down? expanded by a join?) — an unexpected jump or drop pinpoints the broken stage immediately.
3. Validate grain after every aggregation. After a GROUP BY, confirm the result really is one row per the intended key with SELECT key, COUNT(*) FROM result GROUP BY key HAVING COUNT(*) > 1 — any row returned means the aggregation didn't fully collapse to the grain you expected.
4. Check for duplicates after joins. Immediately after any join, re-run the duplicate-multiplication check from Correctness above — catching it right after the join that caused it is far faster than tracing it back from a wrong final total.
5. Use sample IDs to trace through the whole pipeline. Pick one known, well-understood row (e.g. order 5001, order 5005 the orphan) and follow it through every CTE stage by hand — it should appear (or intentionally disappear, and you should know exactly where and why) at each step.
6. Use EXPLAIN once correctness is confirmed. Only reach for performance tuning after the output is verified correct — a fast wrong answer is strictly worse than a slow correct one.
7. Add a permanent reconciliation check once the bug is fixed, so the same class of drift is caught automatically next time, rather than only when someone happens to notice a wrong number downstream.

How interviewers trick you with CTEs

• They ask you to filter on a window function result directly in the same SELECT ("just add WHERE rn = 1") to see if you know you need a CTE/subquery layer for that.
• They ask "does using a CTE make this query faster than a subquery?" — the honest, senior-level answer is "usually not by itself; it depends on inlining vs materialization for this specific engine," not a flat yes or no.
• They give you a query with the same CTE referenced 3 times and ask you to estimate cost — testing whether you assume "defined once = computed once."
• They ask you to write a CTE that references itself without saying the word "recursive" — watching whether you catch that it needs WITH RECURSIVE (see Level 5, Recursive CTEs) rather than plain WITH.
• They ask you to convert a deeply nested subquery into CTEs live on a whiteboard — really testing whether you can decompose a problem into clean, independent steps under pressure.

Rapid-fire trick questions

3 Hard Interview Questions

WITH dept_avg AS (
    SELECT d.dept_name, AVG(e.salary) AS avg_salary
    FROM departments d
    JOIN employees e ON e.dept_id = d.dept_id
    GROUP BY d.dept_name
)
SELECT dept_name, avg_salary
FROM dept_avg
ORDER BY avg_salary DESC
LIMIT 1;

WITH dept_avg AS (
    SELECT d.dept_name, AVG(e.salary) AS avg_salary
    FROM departments d
    JOIN employees e ON e.dept_id = d.dept_id
    GROUP BY d.dept_name
),
max_avg AS (
    SELECT MAX(avg_salary) AS top_avg FROM dept_avg
)
SELECT da.dept_name, da.avg_salary
FROM dept_avg da
JOIN max_avg ma ON da.avg_salary = ma.top_avg;

WITH customer_totals AS (
    SELECT c.customer_name, SUM(o.total_amount) AS total_revenue
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_name
),
grand_total AS (
    SELECT SUM(total_revenue) AS company_total FROM customer_totals
)
SELECT
    ct.customer_name,
    ct.total_revenue,
    ROUND(100.0 * ct.total_revenue / gt.company_total, 2) AS pct_of_total
FROM customer_totals ct
CROSS JOIN grand_total gt
ORDER BY pct_of_total DESC;

WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
),
order_with_customer AS (
    SELECT it.order_id, o.customer_id, it.line_total
    FROM item_totals it
    JOIN orders o ON o.order_id = it.order_id   -- BUG lives one layer up, but this looks fine in isolation
),
customer_totals AS (
    SELECT customer_id, SUM(line_total) AS total_revenue
    FROM order_with_customer
    GROUP BY customer_id
)
SELECT c.customer_name, ct.total_revenue
FROM customer_totals ct
JOIN customers c ON c.customer_id = ct.customer_id;
-- Bug: order 5002 (Grace Lin) has no rows in order_items? No — check again: it does (9003).
-- The real bug: order_items has NO row for some future order_id that never got line items yet
-- (e.g. a new order 5006 with $900 total but items not loaded). item_totals silently drops it
-- because GROUP BY only emits rows for order_ids that exist in order_items, and item_totals
-- is INNER JOINed away entirely -- the order's own known total_amount is never used as a fallback.

WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
),
order_with_customer AS (
    SELECT
        o.order_id,
        o.customer_id,
        COALESCE(it.line_total, o.total_amount) AS line_total  -- fallback to header total
    FROM orders o
    LEFT JOIN item_totals it ON it.order_id = o.order_id
)
SELECT customer_id, SUM(line_total) AS total_revenue
FROM order_with_customer
GROUP BY customer_id;

Common interview question bank

A quick-reference map of the CTE questions that come up most often, and exactly where in this module you've already built the technique to answer each one.

Whiteboard patterns

Live-coding CTEs under pressure is a different skill from writing them at a desk — these are the specific transformations interviewers ask for most, with the mental checklist for each.

Break a nested subquery into CTEs

Work from the inside out: the innermost subquery becomes your first CTE; each layer wrapping it becomes the next CTE down the chain, referencing the one before it. Name each one for what it computes before writing its body.

-- Nested (hard to read live)
SELECT * FROM (
    SELECT customer_id, SUM(total_amount) AS revenue
    FROM orders
    WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA')
    GROUP BY customer_id
) t WHERE revenue > 1000;

-- Decomposed on the whiteboard, innermost first
WITH us_customers AS (
    SELECT customer_id FROM customers WHERE country = 'USA'
),
customer_revenue AS (
    SELECT o.customer_id, SUM(o.total_amount) AS revenue
    FROM orders o
    JOIN us_customers uc ON uc.customer_id = o.customer_id
    GROUP BY o.customer_id
)
SELECT * FROM customer_revenue WHERE revenue > 1000;

Build a multi-step funnel analysis

One CTE per funnel stage (visited → signed_up → purchased), each one a set of qualifying IDs, chained with LEFT JOINs so you can count drop-off at every stage in the final SELECT without losing anyone.

WITH visited AS (SELECT DISTINCT user_id FROM events WHERE event_type = 'visit'),
     signed_up AS (SELECT DISTINCT user_id FROM events WHERE event_type = 'signup'),
     purchased AS (SELECT DISTINCT user_id FROM events WHERE event_type = 'purchase')
SELECT
    COUNT(v.user_id) AS visited_count,
    COUNT(s.user_id) AS signed_up_count,
    COUNT(p.user_id) AS purchased_count
FROM visited v
LEFT JOIN signed_up s ON s.user_id = v.user_id
LEFT JOIN purchased p ON p.user_id = v.user_id;

Build a retention/cohort query using CTE stages

Stage 1: assign each user their cohort (e.g. signup month). Stage 2: for each subsequent period, mark whether that user was active. Final: aggregate stage 2 into a cohort × period retention grid. State this three-stage shape out loud before coding — interviewers weight the plan as much as the syntax.

Build a hierarchy traversal

State the anchor and recursive member in plain English first ("start at the root, then repeatedly find children of the previous generation"), then write the WITH RECURSIVE skeleton, then fill in the actual columns — see Recursive CTEs above for the full worked pattern.

Build a dedup query

State the definition of "duplicate" explicitly first ("same order_id + product, keep the earliest item_id") — this is usually the actual point of the question, not the ROW_NUMBER() syntax itself, which follows automatically once the definition is clear (full pattern in Deduplication with a CTE).

Build a reconciliation report

Two CTEs computing the same metric from two different sources/stages, joined and diffed in the final SELECT — the exact shape shown in Production Patterns above, generalizable to "compare system A's count to system B's count."

2 FAANG-Level Questions

WITH max_per_customer AS (
    SELECT customer_id, MAX(total_amount) AS max_amount
    FROM orders
    GROUP BY customer_id
)
SELECT o.customer_id, o.order_id, o.total_amount
FROM orders o
JOIN max_per_customer m
    ON o.customer_id = m.customer_id
   AND o.total_amount = m.max_amount;

WITH orphan_check AS (
    SELECT o.order_id, (c.customer_id IS NULL) AS is_orphaned
    FROM orders o
    LEFT JOIN customers c ON c.customer_id = o.customer_id
),
item_check AS (
    SELECT o.order_id, COUNT(oi.item_id) AS item_count, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS computed_total
    FROM orders o
    LEFT JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY o.order_id
),
combined AS (
    SELECT
        o.order_id,
        oc.is_orphaned,
        ic.item_count = 0 AS has_no_items,
        ABS(ic.computed_total - o.total_amount) > 0.01 AS total_mismatch
    FROM orders o
    JOIN orphan_check oc ON oc.order_id = o.order_id
    JOIN item_check ic ON ic.order_id = o.order_id
)
SELECT *
FROM combined
WHERE is_orphaned OR has_no_items OR total_mismatch;

1 Production Scenario

WITH stg_orders AS (            -- staging: light cleanup only, one-to-one with source
    SELECT order_id, customer_id, total_amount
    FROM orders
),
stg_order_items AS (
    SELECT order_id, quantity, unit_price
    FROM order_items
),
int_order_totals AS (           -- intermediate: business logic, still order-grain
    SELECT
        o.order_id,
        o.customer_id,
        COALESCE(SUM(oi.quantity * oi.unit_price), o.total_amount) AS revenue,
        (SELECT COUNT(*) FROM stg_order_items x WHERE x.order_id = o.order_id) = 0 AS missing_items
    FROM stg_orders o
    LEFT JOIN stg_order_items oi ON TRUE   -- illustrative; real version joins on order_id
    GROUP BY o.order_id, o.customer_id, o.total_amount
),
mart_customer_revenue AS (      -- mart: final grain the dashboard actually queries
    SELECT customer_id, SUM(revenue) AS total_revenue, BOOL_OR(missing_items) AS has_data_gaps
    FROM int_order_totals
    GROUP BY customer_id
)
SELECT * FROM mart_customer_revenue;

10 Practice Questions

Write the SQL yourself before expanding each answer. Use the employees / departments / customers / orders / order_items schema above.

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.emp_name, e.salary
FROM employees e, avg_sal a
WHERE e.salary > a.avg_salary;

WITH revenue AS (
    SELECT customer_id, SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_name, r.total_revenue
FROM customers c
JOIN revenue r ON r.customer_id = c.customer_id;

WITH dept_counts AS (
    SELECT dept_id, COUNT(*) AS headcount FROM employees GROUP BY dept_id
),
order_count AS (
    SELECT COUNT(*) AS total_orders FROM orders
)
SELECT d.dept_name, dc.headcount, oc.total_orders
FROM departments d
LEFT JOIN dept_counts dc ON dc.dept_id = d.dept_id
CROSS JOIN order_count oc;

WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS line_total
    FROM order_items
    GROUP BY order_id
)
SELECT o.order_id, o.total_amount, it.line_total
FROM orders o
JOIN item_totals it ON it.order_id = o.order_id
WHERE o.total_amount <> it.line_total;
-- In our clean dataset this returns 0 rows -- confirming data integrity.

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT d.dept_name, da.avg_salary
FROM departments d
LEFT JOIN dept_avg da ON da.dept_id = d.dept_id;

WITH ranked AS (
    SELECT
        order_id, product, quantity, unit_price,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY unit_price DESC) AS rn
    FROM order_items
)
SELECT order_id, product, unit_price
FROM ranked
WHERE rn = 1;

WITH customer_revenue AS (
    SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_revenue
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.customer_name
),
ranked AS (
    SELECT
        cr1.customer_name,
        cr1.total_revenue,
        (SELECT COUNT(*) FROM customer_revenue cr2 WHERE cr2.total_revenue > cr1.total_revenue) + 1 AS spend_rank
    FROM customer_revenue cr1
)
SELECT * FROM ranked ORDER BY spend_rank;

WITH customer_revenue AS (
    SELECT customer_id, SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
),
avg_revenue AS (
    SELECT AVG(total_revenue) AS avg_rev FROM customer_revenue
)
SELECT c.customer_name, cr.total_revenue
FROM customer_revenue cr
JOIN customers c ON c.customer_id = cr.customer_id
CROSS JOIN avg_revenue a
WHERE cr.total_revenue < a.avg_rev;

SELECT c.customer_name, cr.total_revenue
FROM customers c
JOIN (
    SELECT customer_id, SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
) cr ON cr.customer_id = c.customer_id
WHERE cr.total_revenue < (
    SELECT AVG(t.total_revenue) FROM (
        SELECT SUM(total_amount) AS total_revenue
        FROM orders
        GROUP BY customer_id
    ) t
);
-- Note the average subquery duplicates the aggregation subquery entirely -- exactly
-- the readability/duplication cost that CTEs solve.

WITH customer_check AS (
    SELECT o.order_id, (c.customer_id IS NOT NULL) AS has_valid_customer
    FROM orders o
    LEFT JOIN customers c ON c.customer_id = o.customer_id
),
item_check AS (
    SELECT o.order_id, EXISTS (
        SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id
    ) AS has_items
    FROM orders o
)
SELECT cc.order_id, cc.has_valid_customer, ic.has_items
FROM customer_check cc
JOIN item_check ic ON ic.order_id = cc.order_id;