CTEs: naming each step with WITH

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  O["orders"] --> A["CTE per_customer<br/>total per customer"]
  A --> M["main query:<br/>join names,<br/>keep totals above average"]
  A -->|"used again"| AV["average of the totals"]
  AV --> M
  C["customers"] --> M

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.

The shape of it

WITH step_one AS (
  SELECT ...
),
step_two AS (
  SELECT ... FROM step_one ...
)
SELECT ...
FROM step_two;

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

WITH per_customer AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, p.total
FROM per_customer p
JOIN customers c ON c.customer_id = p.customer_id
WHERE p.total > (SELECT AVG(total) FROM per_customer)
ORDER BY p.total DESC;

WITH delivered AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'delivered'
),
totals AS (
  SELECT customer_id, COUNT(*) AS delivered_orders, SUM(amount) AS delivered_total
  FROM delivered
  GROUP BY customer_id
)
SELECT c.name, t.delivered_orders, t.delivered_total,
       CASE WHEN t.delivered_total >= 200 THEN 'gold' ELSE 'standard' END AS tier
FROM totals t
JOIN customers c ON c.customer_id = t.customer_id
ORDER BY t.delivered_total DESC, c.name;

Must-know 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.

In other databases

• PostgreSQL before version 12 always computed a CTE separately (an "optimisation fence"); since 12 it can fold it into the main query, and you can force either with MATERIALIZED / NOT MATERIALIZED.
• MySQL supports CTEs from version 8.0.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 3: Writing

  Harvard, free

• Read

  PostgreSQL docs — WITH Queries (Common Table Expressions)

  official documentation, free

• Read

  Mode SQL Tutorial — Writing Subqueries in SQL

  Mode, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why a CTE makes a query easier to read than nested subqueries.

   Show answer

   Nested subqueries read inside out: you have to find the innermost bracket first and work outwards, and a step needed twice has to be written twice.

   A CTE reads top to bottom, like a recipe. Each step gets a name that says what it is (per_customer, delivered, totals), and later steps use it like a table. In the first example, per_customer is used twice, once to join and once for the average, but written only once. Each step can also be run and checked on its own while you build the query.

   (It is about readability. In most databases a CTE is not faster than the same subquery.)

2. Writefrom a blank page, then run it

   Using two CTEs, find the product category with the highest revenue.

   Show answer

   Step 1 (line_revenue): the revenue of each item line, with its category. Step 2 (category_revenue): add those up per category. Then keep the category whose revenue equals the maximum. Electronics wins with £440: 11 headphones at £40.

   Using = MAX(…) instead of ORDER BY … LIMIT 1 keeps both categories if two ever tie.

   sqlCopy

   WITH line_revenue AS (
     SELECT p.category, oi.quantity * p.price AS revenue
     FROM order_items oi
     JOIN products p ON p.product_id = oi.product_id
   ),
   category_revenue AS (
     SELECT category, SUM(revenue) AS revenue
     FROM line_revenue
     GROUP BY category
   )
   SELECT category, revenue
   FROM category_revenue
   WHERE revenue = (SELECT MAX(revenue) FROM category_revenue);

   Try it

   1 row

   category	revenue
   Electronics	440

3. Predictsay it before you run it

   In the first example, what changes if > becomes <?

   Show answer

   You get the customers who spend less than average, instead of more: Lena (£60) and Zoe (£33), instead of Priya (£425) and Tom (£275).

   The per-customer totals are 425, 275, 60 and 33, and their average is 793 ÷ 4 = 198.25. Omar and the second Tom are not in per_customer at all (they have no orders), so they are not below the average either.

   sqlCopy

   WITH per_customer AS (
     SELECT customer_id, SUM(amount) AS total
     FROM orders
     GROUP BY customer_id
   )
   SELECT c.name, p.total
   FROM per_customer p
   JOIN customers c ON c.customer_id = p.customer_id
   WHERE p.total < (SELECT AVG(total) FROM per_customer)
   ORDER BY p.total DESC;

   Try it

   2 rows

   name	total
   Lena	60
   Zoe	33

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Customer 3 has an order with a NULL amount. Does it affect per_customer's total for Lena, or the average? Explain.

   Show answer

   Not here.

   SUM skips NULLs, so Lena's total is just her £60 order. The NULL is ignored: her total is not NULL and not lower. The average is then taken over the 4 totals (425, 275, 60, 33), so it is 198.25 either way.

   It would matter if all of a customer's amounts were NULL. Their total would be NULL, and AVG would skip that customer entirely: the average would be over 3 customers, not 4. If that customer should count as £0, use COALESCE(SUM(amount), 0).

   sqlCopy

   WITH per_customer AS (
     SELECT customer_id, SUM(amount) AS total,
            COUNT(*) AS orders, COUNT(amount) AS with_amount
     FROM orders
     GROUP BY customer_id
   )
   SELECT customer_id, total, orders, with_amount,
          (SELECT AVG(total) FROM per_customer) AS avg_total
   FROM per_customer
   ORDER BY customer_id;

   Try it

   4 rows

   customer_id	total	orders	with_amount	avg_total
   1	425	5	5	198.25
   2	275	2	2	198.25
   3	60	2	1	198.25
   5	33	1	1	198.25

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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.

Follow-up questions

Tick each check when you can do it without notes