SQL-01-012-P · Primer — Common Table Expressions (WITH)

The idea in one sentence

A Common Table Expression is a way to give a name to a query and then use that name as if it were a table — WITH totals AS (SELECT ...) SELECT ... FROM totals — so instead of nesting a subquery inside a subquery inside your FROM, you write each step on its own, in reading order, with a label that says what it is.

Why it exists

Real questions rarely fit in one flat SELECT. "Which customers spent more than their region's average?" is secretly two questions stacked: first work out each customer's total, then compare that total against a number you have to compute from those same totals. The classic way to express "a query that feeds another query" is the subquery — a SELECT tucked inside parentheses in the FROM or WHERE. It works, but it reads inside-out: the thing that runs first is buried deepest, the outer query wraps around it, and by the third level of nesting nobody — including the person who wrote it — can tell what depends on what.

CTEs exist to flip that inside-out mess right-side up. A WITH clause lets you pull each intermediate result out to the top, give it a plain-English name, and lay the steps out top-to-bottom in the order you'd explain them to a colleague: first the per-customer totals, then the per-region averages, then the comparison. The database does the same work either way — a CTE is not usually faster or slower than the equivalent subquery — but the query becomes something a human can read, review, and change without unpicking a knot of nested parentheses. That readability is the whole point, and it's why CTEs are one of the most-loved features in everyday SQL.

How it works

A WITH clause sits before your main SELECT and defines one or more named result sets, each written as name AS (query). Once named, you use each name in your FROM or JOIN exactly like a table. You can chain them: a second CTE is allowed to reference the first, a third can reference the second, so each step builds on the one above it — this is how you decompose a hard query into a readable pipeline. The names live only for the duration of that single statement; nothing is stored on disk. Order matters for reading but the whole thing still runs as one query, so you get one consistent snapshot of the data. Below, customer_totals sums each customer's spend, region_avg averages those totals per region, and the final SELECT joins the two to keep only customers who beat their region's average:

CREATE TABLE orders (id INTEGER, customer TEXT, region TEXT, amount INTEGER);
INSERT INTO orders VALUES
  (1,'Ana', 'East',  40), (2,'Ana', 'East',  70),   -- Ana  East = 110
  (3,'Ben', 'East',  90), (4,'Ben', 'East',  30),   -- Ben  East = 120
  (5,'Cleo','West', 200),                            -- Cleo West = 200
  (6,'Dara','West',  50), (7,'Dara','West',  10);    -- Dara West =  60

WITH customer_totals AS (          -- step 1: spend per customer
    SELECT customer, region, SUM(amount) AS spent
    FROM orders
    GROUP BY customer, region
),
region_avg AS (                    -- step 2: average of THOSE totals, per region
    SELECT region, AVG(spent) AS avg_spent
    FROM customer_totals           -- chained: reads step 1
    GROUP BY region
)
SELECT c.customer, c.region, c.spent, r.avg_spent   -- step 3: compare
FROM customer_totals AS c
JOIN region_avg      AS r ON r.region = c.region
WHERE c.spent > r.avg_spent
ORDER BY c.region, c.spent DESC;

-- customer  region  spent  avg_spent
-- --------  ------  -----  ---------
-- Ben       East    120    115.0        (East avg = (110+120)/2 = 115)
-- Cleo      West    200    130.0        (West avg = (200+ 60)/2 = 130)
-- Ana (110<115) and Dara (60<130) fall below their region average → dropped

Read it top to bottom and it tells the story in order: totals, then averages of the totals, then the comparison — no nesting required.

The one gotcha

A CTE's name lives for exactly one statement, and the CTEs are only defined at the top — they don't run first as standing tables. Two beginner traps follow from this. First, scope: you cannot define customer_totals in one statement and then SELECT * FROM customer_totals in a separate statement later — the name evaporates the instant the semicolon lands. If you need it to persist, you want a VIEW or a temporary table, not a CTE. Second, and subtler: a CTE looks like it "materializes" a table you can reuse for free, but that's not guaranteed. If the same CTE is referenced several times, some databases (older PostgreSQL, by default) will re-run its query each time it's mentioned rather than compute it once — so a CTE is a naming and readability tool, not a performance guarantee. Don't reach for a CTE expecting it to cache expensive work; reach for it because it makes the query readable. The rule that keeps you safe: a CTE renames a query for the length of one statement — it does not save a result, and it does not promise the work runs only once.

See it — a chained CTE pipeline reads top to bottom

flowchart TB
    A["orders table<br/>(7 raw rows)"] --> B["CTE 1: customer_totals<br/>SUM(amount) per customer"]
    B --> C["CTE 2: region_avg<br/>AVG(spent) per region<br/>(reads CTE 1)"]
    B --> D["Final SELECT<br/>join totals to averages"]
    C --> D
    D --> E["WHERE spent > avg_spent<br/>keep above-average<br/>customers"]
    E --> F["Result rows<br/>Ben, Cleo"]

The one-line takeaway

WITH name AS (query) names an intermediate result so you can use it like a table in the same statement — turning inside-out nested subqueries into a top-to-bottom pipeline that reads like the steps you'd explain out loud.

Where you'll meet it

You'll meet CTEs the first time a subquery grows a subquery of its own and you realize you can no longer see where one ends and the next begins — that's the moment to lift each step into a named WITH block. They're everywhere in analytics and reporting: any "compute a number, then compare rows against it" question (above-average customers, top-N per group, week-over-week change) is cleanest as a small chain of CTEs. Data-engineering and analyst interviews lean on them constantly, both as a direct ask ("rewrite this nested query using a CTE") and as a readability signal — an interviewer watching you decompose a gnarly question into named steps is watching for exactly the clarity CTEs give. Expect the two classic follow-ups: "what's the difference between a CTE and a subquery?" (mostly readability — same work, clearer shape) and "how is a CTE different from a view or a temp table?" (a CTE lives for one statement only; a view is a saved, reusable definition). Get comfortable chaining them, and even intimidating multi-step queries become a calm list of steps you can read from the top.

Named concepts: WITH clause, named result sets, readability vs subqueries, chained CTEs