Stop 03 · SQL for Data AnalysisTopics · 6/10

Common Table Expressions (CTEs)

In short

Long queries become unreadable fast. A CTE lets you name each step at the top, so your query reads like a recipe, and the next person can follow it.

A WITH clause that names a temporary result set for readability — functionally similar to a subquery, but far easier to read and reuse within a long, complex query.

The jaadu lines up small prep bowls of chopped vegetables and spices beside a stove.

Think of it like this

Prep bowls before cooking

Good cooks chop onions, tomatoes and spices into separate bowls before lighting the stove. Then cooking is just 'add bowl one, then bowl two'. A CTE is a prep bowl: a named, ready result you use in the final query.

Watch it happen

A query built from named steps

paid_orders

per_customer

Final SELECT

1/3First CTE: paid_orders keeps only paid orders from this year.

The big ideas

What you need to know

WITH name AS (...)

Starts with WITH, gives a result a name, and the main query uses it like a table.

Chain several

Separate CTEs with commas. Each can use the ones defined above it.

Readability first

CTEs mostly make queries easier to read and debug. Performance is usually similar to subqueries.

Easy to debug

Run SELECT * FROM one CTE on its own to check each step before moving on.

See it in code

Walk through the code

sql

1WITH paid_orders AS (2  SELECT customer_id, amount3  FROM orders4  WHERE status = 'paid' AND order_date >= '2026-01-01'5),6per_customer AS (7  SELECT customer_id, SUM(amount) AS total_spent8  FROM paid_orders9  GROUP BY customer_id10)11SELECT customer_id, total_spent12FROM per_customer13ORDER BY total_spent DESC14LIMIT 10;

1/3Step one: filter to paid orders this year and name it paid_orders.

Top 10 spenders this year, split into two readable steps.

Save yourself the pain

Common mistakes

Oops

Forgetting the comma between two CTEs.

Do this instead

WITH a AS (...), b AS (...) SELECT ... The comma separates CTEs. No comma before the final SELECT.

Oops

Giving CTEs names like t1, t2, x.

Do this instead

Use names that say what's inside: paid_orders, monthly_revenue, active_users.

Your mission

Refactor a messy query

~30 min

Quick check

What's the main reason analysts use CTEs?

Go deeper

1Rewrite a nested subquery from the previous topic as a CTE and compare readability.
2Practice chaining 2-3 CTEs together in a single query.
3Adopt CTEs as your default for any query beyond a couple of joins.