Common Table Expressions (CTEs) With WITH

Overview

A Common Table Expression, defined with WITH ... AS (...), lets you give a name to an intermediate query result and then reference that name later in the same statement, as if it were a temporary table. This makes multi-step logic readable: instead of nesting subqueries inside subqueries, you build up your answer in named stages.

A typical use case is computing a per-customer metric — like total money spent — that requires a join and an aggregation, and then using that computed metric in a further filter or join without repeating the aggregation logic.

The Query

Step-by-Step Breakdown

1. WITH customer_spend AS (...) — defines a named result set. Inside it, orders is joined to products so we can multiply quantity * price per line item, then grouped by customer_id to get one total per customer.
2. Once defined, customer_spend behaves like any other table for the rest of the statement — you can SELECT from it, JOIN it, or filter it.
3. SELECT c.name, cs.total_spend FROM customer_spend cs JOIN customers c ON c.id = cs.customer_id — the outer query joins the pre-computed spend totals back to customers purely to get a readable name.
4. ORDER BY cs.total_spend DESC — highest spenders first.

The CTE is evaluated once and referenced by name, which keeps the aggregation logic separate from — and easier to read than — the final join and sort.

Variations

Chain a second CTE that filters down to big spenders only:

Each CTE can reference the ones defined before it, separated by commas after the first WITH. Here, big_spenders reads from customer_spend the same way the final SELECT would read from a real table.

Common Mistakes

• Thinking a CTE persists after the statement. It only exists for the duration of the single query it's attached to — you can't reference customer_spend in a later, separate query.
• Forgetting the comma between multiple CTEs. WITH a AS (...) b AS (...) is invalid; it must be WITH a AS (...), b AS (...).
• Using a CTE where a simple subquery would be clearer. For a single, small filter, an inline subquery may be easier to read than a named CTE — reach for CTEs when you have multiple steps or want to reuse a result more than once in the same query.
• Assuming CTEs can be referenced recursively without extra syntax. Self-referencing (recursive) CTEs need an explicit WITH RECURSIVE declaration; a plain WITH CTE can only reference CTEs defined earlier in the same clause, not itself.