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.