Can you read it tomorrow?
A few days ago Hermione asked Harry for a list: customers whose total spend is above average — the kind of thing that decides who gets invited into NOVA’s loyalty tier. Harry has been heads-down on it since. Today he walks over, laptop open, looking pleased with himself.
It works.
Show me.
SELECT c.name, c.country, ot.total
FROM customers c
JOIN (
SELECT customer_id, ROUND(SUM(amount), 2) AS total
FROM orders
GROUP BY customer_id
) ot ON ot.customer_id = c.id
WHERE ot.total > (
SELECT AVG(total)
FROM (
SELECT ROUND(SUM(amount), 2) AS total
FROM orders
GROUP BY customer_id
) inner_totals
)
ORDER BY ot.total DESC;The total order amount per customer, compared against the average of those totals — computed twice, once inline for the join, once again inside the WHERE clause, because there wasn’t an obvious way to reuse the first calculation.
Running…
Can you read it tomorrow?
...honestly? Not without staring at it for five minutes first.
Exactly the problem. You didn't just solve this for the database — you have to be able to trust it next week, and so does whoever reads it after you.
The query isn’t wrong. It’s just written inside-out — the same calculation duplicated, buried two parentheses deep in two different places. A common table expression, written with WITH, lets you name an intermediate result once, at the top, and then refer to it by that name as many times as you need — anywhere a table could go.
Name the messy middle step. Then read the query top to bottom.
The repeated piece, named and previewed on its own:
WITH customer_totals AS (
SELECT customer_id, ROUND(SUM(amount), 2) AS total
FROM orders
GROUP BY customer_id
)
SELECT * FROM customer_totals
ORDER BY customer_id;| customer_id | total |
|---|---|
| 1 | 257.97 |
| 2 | 229.98 |
| 4 | 49.99 |
| 5 | 250.46 |
| 6 | 59.99 |
| 7 | 259.97 |
| 9 | 24.99 |
| 10 | 177.99 |
| 11 | 143.99 |
| 12 | 34.5 |
customer_totals isn’t a table in the database — it only exists for the duration of this one query. But once it’s named, it behaves like one:
WITH customer_totals AS (
SELECT customer_id, ROUND(SUM(amount), 2) AS total
FROM orders
GROUP BY customer_id
)
SELECT c.name, c.country, ct.total
FROM customers c
JOIN customer_totals ct ON ct.customer_id = c.id
WHERE ct.total > (SELECT AVG(total) FROM customer_totals)
ORDER BY ct.total DESC;customer_totals shows up twice here — once to join against customers, once inside the scalar subquery that computes the average — and each time it’s just a name, not a repeated block of GROUP BY logic. Same subquery idea as last chapter; it’s just a subquery against a named step instead of a raw table.
Running…
Same five customers, same totals, as the nested version above. The database doesn’t care which style you write — a WITH block is rewritten into the same query plan either way. The only thing that changed is whether a human can follow it.
Try it: What if you split this into two named steps instead of one — totals, then above_average?
Run your query to see results here.
Same five rows again. A WITH block can define more than one CTE, separated by commas, and later ones can reference earlier ones — above_average here is built entirely out of customer_totals. Each name is a small, readable step; the final SELECT just picks up where the last one left off.
New question, same trick: find customers whose number of orders is above the average number of orders per customer — not total spend this time, just how often they’ve ordered. Return each customer’s name and their order count. Write it with a CTE, not a nested subquery.
Hint 1
You need two numbers: how many orders each customer placed, and the average of that count across all customers. Which one should you name first?
Hint 2
Start with WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) — then reference order_counts twice: once to join against customers, once inside a subquery for the average.
Hint 3
WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) SELECT c.name, oc.order_count FROM customers c JOIN order_counts oc ON oc.customer_id = c.id WHERE oc.order_count > (SELECT AVG(order_count) FROM order_counts);
Solution
Running…
Emma, Alice, and Julia all order more often than the NOVA average — a completely different ranking from the “total spend” version earlier, which is exactly why naming the intermediate step mattered: swap SUM(amount) for COUNT(*) in one named place, and the rest of the query barely changes. A CTE doesn’t let you do anything a nested subquery couldn’t — it just turns a query you have to decode back into one you can read.
WITH names an intermediate result so you can write it once and refer to it by name wherever you need it — the same result as a nested subquery, just a query that argues with itself less.