Common Table Expressions (WITH)

A common table expression, or CTE, gives a name to a query so that you can use it like a table in the statement that follows. CTEs turn a deeply nested query into a list of steps you can read from top to bottom.

Syntax

WITH order_totals AS (
    SELECT oi.order_id,
           SUM(oi.quantity * p.price) AS order_total
    FROM order_items AS oi
    JOIN products AS p ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT order_id, order_total
FROM order_totals
ORDER BY order_total DESC;

The part inside the brackets is an ordinary query. order_totals exists only for this one statement.

Several steps

Separate CTEs with commas. Each one can use those defined before it.

WITH order_totals AS (
    SELECT oi.order_id,
           SUM(oi.quantity * p.price) AS order_total
    FROM order_items AS oi
    JOIN products AS p ON p.product_id = oi.product_id
    GROUP BY oi.order_id
),
customer_totals AS (
    SELECT o.customer_id,
           COUNT(*)            AS orders,
           SUM(ot.order_total) AS revenue
    FROM orders AS o
    JOIN order_totals AS ot ON ot.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.customer_id
)
SELECT c.name,
       ct.orders,
       ct.revenue,
       ROUND(ct.revenue / ct.orders, 2) AS average_order_value
FROM customer_totals AS ct
JOIN customers AS c ON c.customer_id = ct.customer_id
ORDER BY ct.revenue DESC;

Read it as three steps: the value of each order, then totals per customer, then the final report. Each step can be tested alone by changing the last SELECT to read from it.

Using a CTE more than once

Unlike a subquery, a CTE can be referenced several times. Here it is used for the rows and for the overall average:

WITH order_totals AS (
    SELECT oi.order_id,
           SUM(oi.quantity * p.price) AS order_total
    FROM order_items AS oi
    JOIN products AS p ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT order_id, order_total
FROM order_totals
WHERE order_total > (SELECT AVG(order_total) FROM order_totals)
ORDER BY order_total DESC;

Recursive CTEs

A recursive CTE refers to itself. It has a starting query, UNION ALL, and a query that produces the next rows from the previous ones. It stops when the second query returns nothing.

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

A practical use is building a list of dates, so that a report shows days with no orders too:

WITH RECURSIVE days(day) AS (
    SELECT '2025-03-01'
    UNION ALL
    SELECT date(day, '+1 day') FROM days WHERE day < '2025-03-07'
)
SELECT d.day,
       COUNT(o.order_id) AS orders
FROM days AS d
LEFT JOIN orders AS o ON o.order_date = d.day
GROUP BY d.day;

Recursive CTEs are also used to walk hierarchies such as an employee and manager table or a category tree.

When to use a CTE

• A subquery is needed more than once.
• A query has more than two levels of nesting.
• You want each step to have a name that explains it.

Practice

Using CTEs, find the category with the highest revenue from orders that were not cancelled.