301 — Common Table Expressions (CTEs)

Advanced

Learn to write cleaner, more readable SQL using Common Table Expressions. Refactor tangled subqueries into named building blocks, chain multiple CTEs together, and explore recursive CTEs to traverse hierarchical data like category trees.

Learning Objectives

Write CTEs using WITH ... AS syntax

Refactor complex subqueries into readable CTEs

Chain multiple CTEs that reference each other

Understand when to use CTEs vs subqueries vs temp tables

Write recursive CTEs to traverse hierarchical data

Navigate category trees using recursive CTEs with depth limits

Name each query stage clearly so the grain of every CTE is obvious

Step 1

Your first CTE

A Common Table Expression (CTE) lets you name a query and reference it like a table. The syntax is WITH name AS (query). Start with a simple example: pull out a subquery into a CTE.

Commands to Run

sqlite3 ecommerce.db "WITH high_value_orders AS (SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id HAVING total_spent > 500) SELECT c.first_name, c.last_name, h.order_count, h.total_spent FROM high_value_orders h JOIN customers c ON c.id = h.customer_id ORDER BY h.total_spent DESC;"

What This Does

Think of a CTE as a variable for a query. Just like you'd assign a value to a variable in Python or JavaScript to reuse it, WITH high_value_orders AS (...) assigns a name to a result set. The database runs the CTE first, stores the result temporarily, then uses it in the main query. The CTE only exists for the duration of the statement — it vanishes after the semicolon.

Expected Outcome

A list of customers who have spent more than $500 total, showing their name, order count, and total spent. Results are sorted with the highest spenders first.

Pro Tips

1
CTEs always start with the WITH keyword, followed by a name, then AS, then the query in parentheses
2
The CTE name can be used anywhere you'd use a table name in the main query
3
CTE names follow the same rules as table names — use snake_case for readability

Common Mistakes to Avoid

⚠️Forgetting the parentheses around the CTE query: WITH name AS SELECT... will fail — you need WITH name AS (SELECT...)
⚠️Putting a semicolon after the CTE definition and before the main query — the entire WITH...SELECT is one statement

Was this step helpful?