Common Table Expressions (WITH)

A common table expression (CTE) names a subquery at the top of a query with WITH, so the main query can use it like a table. CTEs turn deeply nested queries into readable steps.

Syntax

WITH name AS (
  SELECT ...
)
SELECT ... FROM name;

Example

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
)
SELECT o.id, c.name, ot.total
FROM order_totals ot
JOIN orders o    ON o.id = ot.order_id
JOIN customers c ON c.id = o.customer_id
WHERE ot.total > 200
ORDER BY ot.total DESC;

Expected output

+----+-------------------+--------+
| id |       name        | total  |
+----+-------------------+--------+
|  4 | Grace Hopper      | 583.98 |
|  2 | Ada Lovelace      | 249.99 |
|  7 | Margaret Hamilton |    241 |
|  3 | Linus Torvalds    |  234.5 |
+----+-------------------+--------+

The CTE computes each order's total once, and the main query joins and filters it. Compare the same query with a nested subquery in FROM — same result, harder to read.

Several CTEs, building on each other

WITH
order_totals AS (
  SELECT o.id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id, o.customer_id
),
customer_totals AS (
  SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
  FROM order_totals
  GROUP BY customer_id
)
SELECT c.name, ct.orders, ROUND(ct.spent, 2) AS spent,
       ROUND(ct.spent / ct.orders, 2) AS avg_order
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
ORDER BY spent DESC;

+-------------------+--------+--------+-----------+
|       name        | orders | spent  | avg_order |
+-------------------+--------+--------+-----------+
| Ada Lovelace      |      3 | 589.69 |    196.56 |
| Grace Hopper      |      1 | 583.98 |    583.98 |
| Margaret Hamilton |      1 |  241.0 |     241.0 |
| Linus Torvalds    |      1 |  234.5 |     234.5 |
| Alan Turing       |      1 |   89.0 |      89.0 |
+-------------------+--------+--------+-----------+

Each CTE is one clear step: order totals → customer totals → final report. Later CTEs can use earlier ones. This style is the norm in analytics SQL (and tools like dbt are built around it).

Tips

• Name CTEs after what they contain: order_totals, active_customers.
• Build and test a long query one CTE at a time: SELECT * FROM order_totals to check each step.
• CTEs can also be recursive, which Level 3 covers.

Practice

Using a CTE, find the average number of items (total quantity) per order.

WITH items_per_order AS (
  SELECT order_id, SUM(quantity) AS items
  FROM order_items
  GROUP BY order_id
)
SELECT ROUND(AVG(items), 2) AS avg_items_per_order
FROM items_per_order;

+---------------------+
| avg_items_per_order |
+---------------------+
|                2.13 |
+---------------------+