Common Table Expressions
By the end of this lesson
Break a complex query into readable named steps.
A common table expression, or CTE, gives a name to a query so the main query can refer to it. You write it with WITH, before the SELECT that uses it.
It solves a readability problem rather than a capability one. Almost every CTE could be written as a nested subquery. The difference is that a CTE reads top to bottom, as named steps, instead of inside out.
-- Nested: you have to read the middle first to understand the outside
SELECT c.company_name, totals.revenue
FROM customers AS c
JOIN (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
) AS totals ON totals.customer_id = c.customer_id
WHERE totals.revenue > 25000;
-- As a CTE: one named step, then the query that uses it
WITH customer_revenue AS (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
)
SELECT c.company_name, cr.revenue
FROM customers AS c
JOIN customer_revenue AS cr ON cr.customer_id = c.customer_id
WHERE cr.revenue > 25000
ORDER BY cr.revenue DESC;- Both queries return the same rows and usually produce the same execution plan. The CTE is not faster; it is clearer.
- The name customer_revenue documents what the step produces. A derived table alias does the same job, but it sits at the bottom of a nested block where it is easy to lose.
- A CTE ends where the main statement begins. It exists for that one statement only, which is the main difference from a view.
Several steps, chained
This is where CTEs earn their place. You can define more than one, separated by commas, and each can reference the ones above it. A report that would be four levels of nesting becomes four named steps in reading order.
WITH order_goods AS (
-- Step 1: one row per order, with its goods value
SELECT oi.order_id, SUM(oi.quantity * oi.unit_price) AS goods_value
FROM order_items AS oi
GROUP BY oi.order_id
),
order_detail AS (
-- Step 2: attach the customer and the shipping fee, one row per order
SELECT
o.order_id,
o.customer_id,
o.order_date,
og.goods_value,
og.goods_value + o.shipping_fee AS order_total
FROM orders AS o
JOIN order_goods AS og ON og.order_id = o.order_id
WHERE o.status <> 'cancelled'
AND o.order_date >= '2026-01-01'
),
customer_summary AS (
-- Step 3: roll up to one row per customer
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(order_total) AS total_spend,
AVG(order_total) AS average_order
FROM order_detail
GROUP BY customer_id
)
SELECT
c.company_name,
c.country,
cs.order_count,
cs.total_spend,
cs.average_order
FROM customer_summary AS cs
JOIN customers AS c ON c.customer_id = cs.customer_id
WHERE cs.order_count >= 3
ORDER BY cs.total_spend DESC;- Each step does one thing and has a name that says what. You can read it as a paragraph.
- Step 1 collapses order_items to one row per order, which means step 2's join is one row to one row. That is the joins lesson applied: shipping_fee is never multiplied.
- A comment on each CTE costs one line and saves the next reader several minutes.
- Debugging is much easier than with nesting: comment out the final SELECT, replace it with SELECT * FROM order_detail, and inspect the intermediate result directly.
Recursive CTEs walk a hierarchy
A CTE can refer to itself. That is what makes it possible to follow a chain of unknown length — a reporting line, a category tree, a bill of materials.
A recursive CTE has two parts joined by UNION ALL. The anchor produces the starting rows. The recursive part joins back to the CTE to produce the next level, and repeats until it produces no new rows.
WITH reporting_line AS (
-- Anchor: the direct reports of employee 7
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.manager_id,
1 AS depth
FROM employees AS e
WHERE e.manager_id = 7
UNION ALL
-- Recursive: the direct reports of anyone already found
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.manager_id,
rl.depth + 1
FROM employees AS e
JOIN reporting_line AS rl ON rl.employee_id = e.manager_id
)
SELECT employee_id, first_name, last_name, depth
FROM reporting_line
ORDER BY depth, last_name
OPTION (MAXRECURSION 20);- The anchor runs once. The recursive part then runs repeatedly, each time against the rows the previous pass produced, until a pass returns nothing.
- depth is carried through and incremented, which tells you how many levels down each person sits. It is also a useful safety check: unexpected depths mean unexpected data.
- OPTION (MAXRECURSION 20) is SQL Server's guard against runaway recursion. The default is 100 levels; setting it to a realistic number turns an infinite loop into an error you can see. PostgreSQL has no such default, so a cycle in the data runs until you stop it.
- PostgreSQL requires WITH RECURSIVE rather than plain WITH. The rest of the structure is the same.
Summary
- WITH names a query so the statement that follows can read as ordered steps
- Several CTEs can be chained, each building on the last, which replaces deep nesting
- A CTE is a readability tool, not a cache — repeated references may be evaluated repeatedly
- A recursive CTE has an anchor and a self-referencing part, and needs a depth guard against cycles
Practice
Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.
Try it yourself
Try it yourself
Rewrite this as a CTE, and add the product's category to the output: find products whose average sold price across all order items is more than 10% below their current list price.
Show solution
One named step computing the average sold price per product, then a join to products to compare against the list price.
The comparison p.unit_price * 0.90 sits on the products side rather than dividing the average, which keeps the arithmetic readable and avoids a division by zero if a product were somehow priced at zero.
Written as a nested subquery this works equally well. The CTE version is easier to extend — adding a second step, such as units sold, does not require restructuring anything.
Worth noting what the query does not tell you: a large gap might be heavy discounting, or it might be a list price that was increased recently. The query surfaces the products to look at; it does not explain them.
WITH sold_prices AS (
SELECT
oi.product_id,
AVG(oi.unit_price) AS average_sold_price,
SUM(oi.quantity) AS units_sold
FROM order_items AS oi
GROUP BY oi.product_id
)
SELECT
p.product_name,
p.category,
p.unit_price AS list_price,
sp.average_sold_price,
sp.units_sold
FROM products AS p
JOIN sold_prices AS sp ON sp.product_id = p.product_id
WHERE sp.average_sold_price < p.unit_price * 0.90
ORDER BY sp.units_sold DESC;Knowledge check
Nothing is recorded and there is no score. The explanation appears either way.
Saved in this browser only.