Multiple & Chained CTEs

A single WITH clause isn’t limited to one common table expression. You can define several, separated by commas, and — this is the powerful part — each later CTE can reference the ones defined before it. This lets you build a multi-step analysis that reads top to bottom, one clear stage at a time.

Defining multiple CTEs

Two independent CTEs used together

SQL

WITH regional_totals AS (
  SELECT region, SUM(amount) AS total_sales
  FROM sales
  GROUP BY region
),
regional_averages AS (
  SELECT region, AVG(amount) AS avg_sale
  FROM sales
  GROUP BY region
)
SELECT t.region, t.total_sales, a.avg_sale
FROM regional_totals t
JOIN regional_averages a ON t.region = a.region
ORDER BY t.total_sales DESC;

Chaining CTEs — later ones build on earlier ones

This is where CTEs really shine: each step in a multi-stage calculation gets its own name, and the next step just refers to it like a table. Here’s a three-step analysis — aggregate sales by salesperson, rank them, then pick only the top 3:

Aggregate, then rank, then filter to the top N

SQL

WITH salesperson_totals AS (
  -- Step 1: total sales per salesperson
  SELECT salesperson_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY salesperson_id
),
ranked_salespeople AS (
  -- Step 2: rank them by total sales, referencing the CTE above
  SELECT
    salesperson_id,
    total_sales,
    RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
  FROM salesperson_totals
)
-- Step 3: keep only the top 3, referencing the CTE above
SELECT s.name, r.total_sales, r.sales_rank
FROM ranked_salespeople r
JOIN salespeople s ON s.id = r.salesperson_id
WHERE r.sales_rank <= 3
ORDER BY r.sales_rank;

Each CTE does one clear job: salesperson_totals aggregates raw rows, ranked_salespeople ranks those aggregated results, and the final SELECT joins in names and filters down to the top performers. Compare that to trying to write this as one deeply nested subquery — the CTE version reads like a set of numbered steps.

Why break a query into stages like this?

Readability — each CTE has a descriptive name that documents what it computes, so the final query reads almost like prose
Easier debugging — you can select from an individual CTE on its own (temporarily replacing the final SELECT) to check its output in isolation
Reuse within the query — a CTE can be referenced more than once later in the same query without repeating its logic
Avoids deeply nested subqueries — five levels of nested parentheses are hard to read; five named, sequential CTEs are not

CTEs are scoped to one statement

All the CTEs in a WITH clause exist only for the single statement that follows them. They are not saved anywhere and cannot be referenced by a later, separate query — for that, you'd want a view or a temporary table instead.

Tip

A later CTE can reference any CTE defined earlier in the same WITH clause, but not one defined after it — think of them as being evaluated in order, top to bottom, each one able to see what came before.