CTEs: naming each step with WITH
A CTE (common table expression) is a named, temporary result, written at the top of a query with WITH name AS (…). Later parts of the query can use it like a table.
Why it matters
Subqueries inside subqueries read inside-out and quickly become unreadable. In an interview, the person watching needs to follow your thinking. CTEs let you write a hard query as a list of small, named steps, in the order you would explain them.
The idea
WITH per_customer AS (SELECT …) defines a step and gives it a name. The main query after it can use per_customer like a table — even more than once.
You can chain several CTEs, separated by commas, and each one can use the ones above it.
A CTE exists only for that one query. It is mostly about readability: the database runs it much like the equivalent subquery.
A recipe that says "first make the sauce; set it aside" before the main steps. Later steps just say "add the sauce". You name the sub-result once and refer to it by name.
Sauce made once is used everywhere it is needed. A database may compute a CTE once or re-run it each time it is used — that is up to the optimiser — so don't use CTEs as a speed trick.
Syntax
WITH step_one AS (
SELECT ...
),
step_two AS (
SELECT ... FROM step_one ...
)
SELECT ...
FROM step_two;How it works
- per_customer: one row per customer with their total spend (425, 275, 60, 33).
- The main query joins per_customer to customers to get names.
- The WHERE uses per_customer a second time to work out the average total (198.25).
- Customers above that average are kept: Priya and Tom.
- Each step can be tested alone: select from it while you build the query.
Key points
- WITH name AS (query) defines a CTE; separate several with commas, and use WITH only once.
- A CTE can use the CTEs defined above it, and the main query can use any of them, more than once.
- A CTE lasts for one statement only.
- Use CTEs to make the steps of a hard query readable — interviewers love seeing this.
- Build and test one CTE at a time.
Common mistakes
- Writing WITH before every CTE. It is
WITH a AS (…), b AS (…). - A comma after the last CTE, before the main SELECT.
- Expecting a CTE to exist in the next query. It is gone after the statement ends.
Not true: A comma is needed after the last CTE, before the main SELECT.
In fact: Commas go between CTEs only. After the last CTE comes the main SELECT, with no comma.
In the interview
A CTE is a named subquery defined with WITH at the top of a statement, which the rest of the query can reference like a table, even multiple times. I use CTEs to break a hard problem into steps — filter, aggregate, rank, then select — which makes the query easy to read and to debug one step at a time. Performance is usually the same as the equivalent subquery.
Part of Unit 4: Subqueries, CTEs and set operations in the free SQL course.