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

  1. per_customer: one row per customer with their total spend (425, 275, 60, 33).
  2. The main query joins per_customer to customers to get names.
  3. The WHERE uses per_customer a second time to work out the average total (198.25).
  4. Customers above that average are kept: Priya and Tom.
  5. Each step can be tested alone: select from it while you build the query.

Key points

Common mistakes

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.