Lesson 10: Common Table Expressions (WITH)

CTEs (WITH clauses) name a subquery so it can be referenced clearly, and even reused multiple times in the same statement.

Real-time example

Break a complex 'top spenders this month' report into named, readable steps instead of one nested subquery mess.

WITH monthly_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    WHERE created_at >= '2026-07-01'
    GROUP BY customer_id
)
SELECT c.name, mt.total
FROM monthly_totals mt
JOIN customers c ON c.id = mt.customer_id
WHERE mt.total > 2000
ORDER BY mt.total DESC;

What's happening

monthly_totals behaves like a temporary named table for the duration of this one query — it doesn't persist afterward.

Reach for a CTE any time a subquery needs a name to make the query readable to your future self.