CTEs

Why it matters

Analysis queries grow. A question like "which customers spend more than the average customer, and what region are they in?" can turn into a subquery inside a subquery inside a join, and nobody (including you next month) can read it. A common table expression (CTE) lets you name each step and write them top to bottom, in the order you think about the problem. CTEs are the single biggest readability upgrade in analytical SQL, and you will use them in almost every lesson from here on.

The core idea

A CTE is a named, temporary result that exists only for one statement. You define it with WITH name AS (...) before the main SELECT, then use the name as if it were a table.

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 200;

Nothing is stored in the database. When the statement finishes, customer_totals is gone.

Rewriting a nested query

Here is a nested version of "customers whose total is above the average customer total":

SELECT c.name, t.total_spent
FROM customers AS c
JOIN (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS t ON t.customer_id = c.id
WHERE t.total_spent > (
  SELECT AVG(total_spent)
  FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
  ) AS t2
);

The per-customer total appears twice, and you have to read from the inside out. The CTE version defines it once:

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.total_spent
FROM customer_totals AS t
JOIN customers AS c ON c.id = t.customer_id
WHERE t.total_spent > (SELECT AVG(total_spent) FROM customer_totals);

Same result, half the code, and each piece has a name that explains it.

Chaining several CTEs

Separate CTEs with commas. Each one can use the ones defined before it, so you can build a small pipeline:

WITH completed AS (            -- step 1: only the orders that count
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'completed'
),
customer_totals AS (           -- step 2: one row per customer
  SELECT customer_id, SUM(amount) AS total_spent
  FROM completed
  GROUP BY customer_id
),
benchmark AS (                 -- step 3: one number
  SELECT AVG(total_spent) AS avg_spent FROM customer_totals
)
SELECT t.customer_id, t.total_spent
FROM customer_totals AS t
CROSS JOIN benchmark AS b
WHERE t.total_spent > b.avg_spent;

CROSS JOIN with a one-row CTE is a common trick to make a single value available on every row. Note there is only one WITH keyword, at the very start.

A good habit while building a chain: end with SELECT * FROM <latest step> and run it, check the rows look right, then add the next step. This is how analysts debug long queries.

Recursive CTEs (a preview)

WITH RECURSIVE lets a CTE refer to itself. It is used for hierarchies (an org chart, a category tree) and for generating series such as every date in a month:

WITH RECURSIVE days(d) AS (
  SELECT '2026-05-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < '2026-05-07'
)
SELECT d FROM days;

This returns seven dates, which is handy for filling gaps in a daily report by left-joining your data onto it. You do not need recursion for this course's activities, but it is good to recognise it.

CTE, subquery or view?

• CTE: a step used inside one query, especially if used more than once or if it makes the query read top to bottom.
• Subquery: a short, one-off filter like WHERE id IN (SELECT ...).
• View: a step many different queries and people need. It is saved in the database (you met views in SQL Fundamentals).

CTEs are supported in SQLite, PostgreSQL, MySQL 8+, SQL Server, Oracle and the major cloud warehouses. Performance is normally similar to the equivalent subquery; some engines can materialize a CTE once, others inline it.

Common mistakes

• Repeating WITH. Write WITH a AS (...), b AS (...), not WITH a AS (...) WITH b AS (...).
• A trailing comma after the last CTE, right before the main SELECT. That is a syntax error.
• Forgetting the main query. A WITH block on its own does nothing; it must be followed by a SELECT (or INSERT, UPDATE, DELETE).
• Using a CTE in a later, separate statement. It only exists for the statement it is attached to.
• Vague names such as t1, temp. Name the step for what it contains: completed_orders, customer_totals.

Quick recap

• WITH name AS (SELECT ...) defines a temporary named result for one statement.
• Chain steps with commas; later CTEs can use earlier ones.
• CTEs remove duplicated subqueries and make queries read top to bottom.
• Build and check one step at a time.

Try it

Open the Do tab and finish a query that uses a customer_totals CTE twice: once for the rows and once for the average.