Recipe
CTEs
WITH names a query before you use it. A common table expression (CTE) runs first, gets a name, and can then be selected from just like a real table, anywhere later in the same statement.
Query
WITH station_avgs AS (
SELECT station, AVG(price) AS avg_price
FROM dishes
GROUP BY station
)
SELECT station, avg_price
FROM station_avgs
WHERE avg_price > 12
ORDER BY avg_price DESC;↓ result
station avg_price
salad 15
grill 14.5You can stack more than one CTE in the same WITH, separated by commas, and a later one can even build on an earlier one. Each still only runs once, no matter how many times the final query refers to it.
Query
WITH station_totals AS (
SELECT s.section, d.price
FROM dishes d
JOIN stations s ON d.station = s.name
)
SELECT section, SUM(price) AS total_price
FROM station_totals
GROUP BY section
ORDER BY total_price DESC;↓ result
section total_price
hot 29
cold 46A CTE and a subquery can often do the same job. The difference is readability: a CTE gives a mid-step a name you can see, instead of burying it inside another query's parentheses.