Naming Subqueries with WITH

Subqueries in FROM can make a query hard to read. You have to read it from the inside out, and with every subquery, it nests deeper. WITH lets you write the subquery first, give it a name, and then use the name like a table:

WITH totals AS (
    SELECT order_id, SUM(quantity * price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT ROUND(AVG(total), 2) AS average_order
FROM totals;

It's the average order from the previous exercise, but now it reads from top to bottom: first, compute the totals of the orders and call them totals; then compute their average. The part WITH name AS (query) is called a common table expression, or CTE for short. It belongs to the query that follows it: together they are one statement with one semicolon at the end, and the name totals exists only within that statement.

A named query can be used more than once. The biggest order is the one whose total equals the biggest total:

WITH totals AS (
    SELECT order_id, SUM(quantity * price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT order_id, total
FROM totals
WHERE total = (SELECT MAX(total) FROM totals);

Without WITH, you'd have to write the query that computes the totals twice, once in FROM and once in the subquery in WHERE.

A WITH can name several queries, separated by commas, with the word WITH only at the start. Each of them can use the ones before it. Here, paid keeps the paid orders, paid_totals computes their totals, and the main query adds them up:

WITH paid AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
),
paid_totals AS (
    SELECT i.order_id, SUM(i.quantity * i.price) AS total
    FROM order_items AS i
    JOIN paid AS p ON p.id = i.order_id
    GROUP BY i.order_id
)
SELECT COUNT(*) AS paid_orders, SUM(total) AS revenue
FROM paid_totals;

Typical mistakes: a comma after the last named query, just before the main SELECT (near "SELECT": syntax error), and a semicolon after its closing parenthesis, which ends the statement before the main query has begun (near ";": syntax error).