Common Table Expressions (CTEs)

Name your query steps with WITH — no more nested subquery spaghetti

Advanced 16 minCTEWITH

WITH ... AS: Named Steps

A CTE (Common Table Expression) is a named temporary result set defined with WITH. You reference it by name in the main query as if it were a regular table. Here's why CTEs matter:

Readability over nesting — Instead of 3 levels of deeply nested subqueries, write labeled steps: WITH step1, step2, step3 → final SELECT. The logic reads top-to-bottom like a recipe instead of inside-out like nested parentheses.

Chain multiple CTEs — Separate them with commas. Each CTE can reference any CTE defined before it in the chain, creating a clean data pipeline where each step builds on the previous one.

Temporary by design — CTEs exist only for the duration of the single query. They don't create permanent tables or views in the database. They're syntactic sugar over derived tables, with typically identical execution performance.

Knowledge Canvas

How CTEs Work

Named temporary result sets

WITH name AS (query) — define a named step
Chain multiple CTEs with commas
Each CTE can reference previously defined CTEs
CTEs exist only for the duration of the query

CTE vs Subquery vs View

Three ways to reuse query logic

CTE: temporary, named, one query

Subquery: inline, anonymous

CTE: readable top-down flow

Subquery: inside-out nesting

CTE: same performance usually

View: permanent, reusable across queries

CTE Pipeline

How multi-step CTEs execute

WITH step1 AS (raw data or first transform)

step2 AS (filter or aggregate step1)

step3 AS (join step2 with other data)

SELECT final result FROM step3

CTE Patterns

Common real-world structures

Multi-step aggregation: raw → grouped → ranked
Recursive CTEs: hierarchies, sequences, trees
Reuse: reference same CTE twice in final SELECT
Self-documenting: CTE names describe each step

Best Practices

Writing clean CTEs

Name CTEs after what they contain, not what they do
Keep each CTE focused on one transformation
Avoid 5+ CTEs — consider views or temp tables
Comment complex CTEs explaining the "why"

Syntax

Syntax Template

1-- Single CTE

2WITH cte_name AS (

3 SELECT

4 ...

6SELECT *

7FROM cte_name;

9-- Multiple CTEs — chain with commas

10WITH step1 AS (

11 SELECT ...

12),

13step2 AS (

14 SELECT *

15 FROM step1

16)

17SELECT *

18FROM step2;

WITHStarts the CTE definition block

AS (...)The query that defines this CTE

Comma-separatedChain multiple CTEs before the final SELECT

Sample Data

orders

12 rows

id	customer_id	total
5001	C1001	59.98
5002	C1002	449
5003	C1001	12.5
5004	C1003	89.99
5005	C1006	245
5006	C1005	159
5007	C1004	45.5
5008	C1009	79
5009	C1012	425
5010	C1007	18.99
5011	C1008	134
5012	C1010	65.5

customers

12 rows

id	name
C1001	Aarav Sharma
C1002	Sara Chen
C1003	James Wilson
C1004	Maria Garcia
C1005	Yuki Tanaka
C1006	Priya Patel
C1007	Alex Johnson
C1008	Chen Wei
C1009	Emma Brown
C1010	Omar Hassan
C1011	Lena Muller
C1012	Ravi Kumar

Worked Example

Find customers who spent above the average total spend.

SQL

1WITH customer_totals AS (

2 SELECT

3 customer_id,

4 SUM(total) AS total_spent

5 FROM orders

6 GROUP BY

7 customer_id

8),

9avg_spend AS (

10 SELECT

11 AVG(total_spent) AS avg_total

12 FROM customer_totals

13)

14SELECT

15 c.name,

16 ct.total_spent

17FROM customer_totals ct

18JOIN customers c

19 ON c.id = ct.customer_id

20CROSS JOIN avg_spend a

21WHERE ct.total_spent > a.avg_total

22ORDER BY

23 ct.total_spent DESC;

Step 1 (customer_totals): sums each customer's orders. Step 2 (avg_spend): averages those totals. Final SELECT: joins with customers for names and filters to above-average spenders. The logic reads top-to-bottom.

Output

3 rows

name	total_spent
Sara Chen	449
Ravi Kumar	425
Priya Patel	245

Key Concepts

1CTEs replace nested subqueries with named, readable steps

2Chain CTEs with commas — each can reference the ones before it

3CTEs don't create tables — they exist only for the query duration

4Performance is typically identical to equivalent subqueries

Pro Tip

Nested subqueries become unreadable past 2–3 levels. CTEs let you name each step so logic reads like a recipe — this is how production analytics SQL is written.

When to Use

Multi-step revenue reports, building metrics from raw data, ranked analyses, any query requiring intermediate computations.

Challenge

Solve the problem below

Using a CTE, find the average order total per customer, then show customers whose average order exceeds 100. Display name and avg_order (rounded to 2 decimals).

Your Query

EXISTS vs IN

Introduction to Window Functions