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 Module 4).

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, reuse & a recursion preview

• 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 Module 4.
• 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.

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.

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 (Module 4) 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.

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;

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;