SQL Practice Lesson 15 of 26

CTEs: naming a subquery with WITH

Back in the subquery lesson, you compared each order's amount against (SELECT AVG(amount) FROM orders) - a query wrapped in parentheses, dropped inline wherever you needed its value. That works, but as queries grow, inline subqueries pile up and get hard to read. A CTE (common table expression) is the same idea with a name attached.

WITH avg_amount AS (SELECT AVG(amount) AS a FROM orders) runs the inner query once, gives the result a name (avg_amount), and lets the rest of the query - the part after WITH ... AS (...) - refer to it like a regular table. It computes exactly the same thing as the inline subquery; it's purely a readability upgrade, not a different result.

Same single orders table (id, item, amount) as the subquery lesson.

Your task: return each order's item and amount where the amount is greater than the average amount across all orders - the same comparison as before, but written as a WITH clause instead of an inline subquery.

You'll practice:

Defining a CTE with WITH name AS (SELECT ...)
Referring to a CTE's column in the outer query, the same way you'd refer to a table's column

-- Return item, amount, for orders above the average amount - using a WITH clause instead of a subquery.
SELECT item, amount FROM orders;