Common Table Expressions (CTEs) in SQL
A Common Table Expression (CTE) defines a named subquery at the top of a statement that the rest of the query can reference by name. Its scope is a single query execution: the name exists only within the statement that defines it.
A CTE lets you name a subquery and put it at the top of your query, where you can read it before anything else runs.
You're writing a monthly revenue report. The query needs to aggregate orders into monthly totals and then filter to show only months above a threshold. Without a CTE, you'd nest the aggregation inside a FROM clause as an anonymous subquery — readable in isolation, but buried inside the query. With a CTE, you name it monthly_revenue, define it at the top, and reference it by name in the main query below. The logic reads in the order it executes.
The WITH keyword opens the definition. After it comes the name, then AS, then the subquery in parentheses:
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT month, revenue
FROM monthly_revenue
WHERE revenue > 10000
ORDER BY monthThe CTE computes the monthly aggregation. The main query filters it. Each layer does one thing, and you can read either in isolation. The CTE name behaves exactly like a table reference in the main query — you can join against it, filter it, aggregate it, or use it in a WHERE clause.
One rule: the CTE name only exists within this one statement. You cannot reference it in a later query, even in the same session. Each query starts fresh.
The biggest advantage over a plain subquery appears when you need the same intermediate result in more than one place. A subquery in FROM has no name — you'd have to copy the entire thing wherever you need it. A CTE is defined once and can be referenced anywhere in the main query by name. You can also use it to test intermediate logic: comment out the main SELECT, add SELECT * FROM monthly_revenue, and run it. You get the intermediate result directly, without executing the rest.
The one thing that trips people up: a CTE is not automatically faster than a derived table.
In PostgreSQL 12 and later, CTEs are inlined by default — the planner treats them as named subqueries and can optimize across the boundary. In older PostgreSQL versions, CTEs were always materialized: executed once, result stored, planner could not push filters inside. If you're on PostgreSQL 11 or earlier, a heavily filtered CTE can block optimizations that matter for large tables. For modern PostgreSQL, the choice between a CTE and a derived table is mostly a readability decision.
A CTE is defined with WITH my_data AS (...). Where can you reference the name my_data?
9 Common Table Expressions (CTEs) practice problems
Write a query to return the count of orders in each status.
Write a query to return the number of active employees in each department.
Write a query to return each user's ID and the number of sessions they have recorded.
Write a query to return the category ID and average price for every category meeting that threshold.
Write a query to return the ID and session count of every user who has recorded more than `5` sessions.
Write a query to return the status, order count, and total revenue for every status whose combined order revenue exceeds `$5,000`.
Write a query to return the department ID and employee count for every department that has `3` or more employees on record.
Write a query to return the user ID and session count for every user whose session count is above the per-user average.
Write a query to return the category ID and average price for every assigned category whose average product price exceeds `$500`.
These problems are part of the Common Table Expressions (CTEs) lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.