Lesson Description
Name a subquery so you can read top-to-bottom.
Lesson 02
Lesson Description
Name a subquery so you can read top-to-bottom.
Easy Project
Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.
In the previous lesson you put a subquery in FROM to aggregate first and filter second. That works, but it reads inside out: your eye has to jump into the parentheses, figure out what the inner query builds, then climb back out to see what happens to it. Once you nest two or three of these, a query becomes hard to read and harder to change.
A common table expression, or CTE, fixes the reading order. You write the intermediate result first, give it a name with WITH, and then write the main query underneath as if that name were a real table. Here's a concrete question from the store database: "What is our average order value, and what are the smallest and largest orders?" The order_items table stores line items, not order totals, so you first need one total per order, and only then can you average those totals. A CTE lets you write exactly those two steps, top to bottom.
WITH cte_name AS (
SELECT ...
FROM ...
)
SELECT ...
FROM cte_name
WHERE ...;
A few rules to keep in mind:
WITH comes first, before the main SELECT.AS.WITH totals (order_id, amount) AS (...).Here's the average order value question:
WITH order_totals AS (
SELECT order_id,
SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
)
SELECT COUNT(*) AS orders,
ROUND(AVG(order_total), 2) AS avg_order_value,
MIN(order_total) AS smallest,
MAX(order_total) AS largest
FROM order_totals;
The result is a single row: 50 orders, an average order value of 133.84, a smallest order of 44.95 and a largest of 349.00. The CTE turns 60 line items into 50 order totals, and the main query summarises those totals. You can't nest aggregates directly (AVG(SUM(...)) is an error in PostgreSQL), so an intermediate step like this is the standard way to get an average of totals.
CTEs behave like tables, so you can join them. In the hospital database, "How many patients from each city have visited, and how many visits does each city account for?"
WITH patient_visits AS (
SELECT patient_id, COUNT(*) AS visits
FROM appointments
GROUP BY patient_id
)
SELECT p.city,
COUNT(*) AS patients,
SUM(pv.visits) AS total_visits
FROM patient_visits pv
JOIN patients p ON p.id = pv.patient_id
GROUP BY p.city
ORDER BY total_visits DESC, p.city;
This returns 5 rows, one per city. Seattle leads with 10 patients and 23 visits, followed by Portland (7 patients, 14 visits), Tacoma (4 patients, 11 visits), Spokane (5 patients, 10 visits) and Bellevue (4 patients, 8 visits). Tacoma has fewer patients than Spokane but more visits, which is the kind of detail you only see once visits are counted per patient first.
This is where CTEs start to beat derived tables. A derived table can be used only in the place where you write it, but a CTE name can appear as many times as you like in the main query. "Which month had the most orders?"
WITH monthly_orders AS (
SELECT DATE_TRUNC('month', order_date)::date AS month,
COUNT(*) AS orders
FROM orders
GROUP BY 1
)
SELECT month, orders
FROM monthly_orders
WHERE orders = (SELECT MAX(orders) FROM monthly_orders);
One row comes back: April 2025 with 13 orders. The store has data for January through May 2025 (7, 10, 10, 13 and 10 orders). The CTE is read twice: once as the main table and once inside the scalar subquery that finds the maximum. Comparing with = to the maximum, rather than using ORDER BY ... LIMIT 1, has a bonus: if two months tied for first place, you would see both.
"For delivered orders only, which countries bring in the most revenue, and what share of the total is that?"
WITH delivered_revenue AS (
SELECT c.country,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.country
)
SELECT country,
revenue,
ROUND(100.0 * revenue / (SELECT SUM(revenue) FROM delivered_revenue), 1) AS pct_of_total
FROM delivered_revenue
ORDER BY revenue DESC
LIMIT 5;
Delivered revenue spans 10 countries and totals 3,944.77. The top five are the United States (529.80, 13.4%), Italy (513.48, 13.0%), the Netherlands (497.99, 12.6%), France (494.90, 12.5%) and the United Kingdom (466.92, 11.8%). Putting the status = 'delivered' filter inside the CTE means both the per-country figures and the grand total are computed from the same filtered data, so the percentages are consistent.
A CTE marked WITH RECURSIVE can refer to itself, which lets you walk hierarchies like an org chart. The HR employees table stores each person's manager_id, so you can start at the top and repeatedly find direct reports:
WITH RECURSIVE org AS (
SELECT id, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, org.level + 1
FROM employees e
JOIN org ON e.manager_id = org.id
)
SELECT level, COUNT(*) AS employees
FROM org
GROUP BY level
ORDER BY level;
The result has 4 rows: 1 person at level 1 (the CEO, Steven King), 14 at level 2, 16 at level 3 and 9 at level 4, adding up to all 40 employees. The first SELECT is the anchor that seeds the result. The second is the recursive step, which runs again and again against the rows found in the previous round until it finds no new ones. You won't need recursion for the exercises here, but it's useful to know the tool exists.
It depends, and PostgreSQL's behaviour changed in version 12.
| Situation | PostgreSQL 12 and later |
|---|---|
| CTE referenced once, no side effects | Usually inlined into the main query, exactly like a derived table |
| CTE referenced two or more times | Materialized: computed once, stored temporarily, then read |
WITH x AS MATERIALIZED (...) | Always computed once and stored |
WITH x AS NOT MATERIALIZED (...) | Always inlined, even if referenced several times |
Inlining is usually what you want, because the planner can then push filters from the main query down into the CTE. Before version 12, every CTE was materialized, which made them an "optimization fence". You will still find old advice online warning that CTEs are slow. On modern PostgreSQL, a CTE used once costs the same as the equivalent subquery.
WITH dept_stats AS (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
)
SELECT COUNT(*) FROM dept_stats;
SELECT * FROM dept_stats;
The first statement works. The second fails with ERROR: relation "dept_stats" does not exist, because the CTE ended with the semicolon. If you need the result in several separate statements, repeat the CTE or create a view.
WITH dept_stats AS (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
),
SELECT * FROM dept_stats;
PostgreSQL reports syntax error at or near "SELECT". Commas separate CTEs from each other; the main query follows the last CTE with no comma. You'll use that comma deliberately in the chained CTEs lesson.
Names like step1, tmp or cte force the reader to open the body to learn what it holds. order_totals, patient_visits and delivered_revenue tell you what each row represents before you read a line of SQL. This isn't an error PostgreSQL will catch, but it's the most common reason CTE-heavy queries become hard to maintain.
CTEs are part of standard SQL and are supported by SQL Server, SQLite (3.8.3 and later) and MySQL (8.0 and later). MySQL 5.7 and earlier have no CTE support, so you'd need derived tables there. SQL Server doesn't accept the RECURSIVE keyword at all: you write plain WITH and it detects the self-reference. The MATERIALIZED and NOT MATERIALIZED hints are PostgreSQL syntax (SQLite also accepts them).
The exercises below ask you to:
dept_stats CTE with average salary and headcount per department, then read from it.Start each one by writing and running just the CTE body, check the rows look right, and only then wrap it in WITH.
MATERIALIZED only when you have a measured reason.WITH RECURSIVE lets a CTE reference itself, the standard way to walk hierarchies such as reporting lines.A common table expression (WITH ...) gives a subquery a name and puts it at the top of the query. Complex logic then reads top to bottom like a series of steps, and each step can be run on its own while you debug.
Order totals as a named step
WITH order_totals AS (
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
)
SELECT o.customer_id, ROUND(AVG(t.total), 2) AS avg_order
FROM order_totals t
JOIN orders o ON o.id = t.order_id
GROUP BY o.customer_id
ORDER BY avg_order DESC;order_totals behaves like a temporary table that exists only for this query.
Department averages compared to each employee
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.first_name, e.salary, ROUND(d.avg_salary, 0) AS dept_avg
FROM employees e
JOIN dept_avg d ON d.department_id = e.department_id
WHERE e.salary > d.avg_salary;Employees who earn more than their own department's average, computed once per department.
Yes. A CTE can be joined or selected from as many times as needed, which is one advantage over repeating a subquery.
Concept check
3 quick questions. One at a time. Instant score at the end.
Using a CTE, return the first_name, last_name, and total_spent of the top 5 customers by total order-item value (quantity * unit_price). Order by total_spent descending.
No results yet
Run a query to see results here
Use a CTE named dept_stats to compute avg_salary and emp_count per department_id. Then return all columns from dept_stats ordered by avg_salary descending.
No results yet
Run a query to see results here
Use a CTE to count appointments per doctor, then join to departments. Return the top 3 department name values and their total appt_count, ordered by appt_count descending.
No results yet
Run a query to see results here