Lesson 8 of 14 · 8 min · Window functions and CTEs
CTEs: writing long queries in readable steps
What you will learn
- Write a query as named steps with
WITH - Chain several CTEs and reuse one in the next
- Combine CTEs with window functions
- Walk a hierarchy with a recursive CTE
- When to use a CTE, a subquery or a temporary table
In simple words
A long recipe is easier to follow when it is split into steps: "first chop the onions, then fry them, then add the rice". A CTE (common table expression) lets you do that in SQL. You give each step a name, and later steps use the earlier ones by that name. The database runs the whole thing as one query.
Words to know
- CTE (common table expression): a named temporary result you define with
WITHfor one query. - Recursive CTE: a CTE that refers to itself, used to walk through hierarchies.
- Anchor: the starting rows of a recursive CTE.
- Hierarchy: data where rows point to a parent row, like employees and managers.
A first CTE
Rewrite the "customers with a paid total above 700" idea in steps:
WITH paid AS (
SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
)
SELECT c.name, p.paid_total
FROM paid p
JOIN customers c ON c.customer_id = p.customer_id
WHERE p.paid_total > 700;
returns Asha with 2500.00. The CTE paid exists only for this statement. It does the same thing a derived table would, but reads top to bottom.
Several CTEs in a chain
Separate CTEs with commas; later ones can use earlier ones. Month-over-month growth without repeating LAG (using monthly_sales from the previous lesson):
WITH changes AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly_sales
)
SELECT month, revenue, prev_revenue,
ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS pct_change
FROM changes;
Top customer per month, in two named steps:
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, customer_id, SUM(amount) AS total
FROM orders
GROUP BY 1, 2
),
ranked AS (
SELECT month, customer_id, total,
ROW_NUMBER() OVER (PARTITION BY month ORDER BY total DESC) AS rn
FROM monthly
)
SELECT month, customer_id, total FROM ranked WHERE rn = 1;
This is the standard shape for analyst queries: aggregate, then rank or compare, then filter. A CTE can be used more than once in the same query (for example in a self-comparison), which a derived table cannot.
Recursive CTEs
Some data is a tree: an employee has a manager who has a manager. A recursive CTE walks it. In PostgreSQL you write WITH RECURSIVE; MySQL 8 also uses WITH RECURSIVE.
WITH RECURSIVE team AS (
SELECT emp_id, name, manager_id, 1 AS level -- anchor: the top person
FROM employees
WHERE name = 'Neha'
UNION ALL
SELECT e.emp_id, e.name, e.manager_id, t.level + 1 -- recursive step
FROM employees e
JOIN team t ON e.manager_id = t.emp_id
)
SELECT name, level FROM team ORDER BY level, name;
name | level
------+-------
Neha | 1
Arun | 2
Sana | 2
The anchor query returns Neha. The recursive part finds people whose manager is already in team, and repeats until it finds no one new. It is useful for org charts, category trees and bill-of-materials. Guard against cycles (a manager chain that loops) with a level limit such as WHERE t.level < 10.
CTE, subquery, temporary table or view?
| Tool | Lives for | Best for |
|---|---|---|
| Subquery | one expression | a single value or a short filter |
| CTE | one statement | breaking a long query into named steps |
| Temporary table | your session | big intermediate results reused many times, which can be indexed |
| View | stored permanently | a query many people or reports reuse |
Performance note: since PostgreSQL 12, a simple non-recursive CTE that is used once can be folded into the main query by the planner, and you can force behaviour with MATERIALIZED or NOT MATERIALIZED. In older versions a CTE was always a fence that was computed first. Treat CTEs as a readability tool first; do not assume one is faster or slower than a subquery without checking the plan.
Writing readable SQL
- Name each CTE for what it holds (
paid_orders,monthly_totals), notaandb. - One idea per CTE.
- Put the final question in the last
SELECT, short and obvious. - Comment the "why" for anything surprising, such as
-- exclude refunded orders (finance rule).
Common mistakes
- Expecting a CTE to persist after the statement ends.
- Forgetting
RECURSIVE(PostgreSQL, MySQL) or theUNION ALLbetween anchor and recursive part. - No stop condition on a recursive CTE where data could loop.
- Too many tiny CTEs for trivial steps, which hides the logic instead of showing it.
- Assuming a CTE is always materialised or always inlined.
Try it yourself
- Rewrite your "orders above the average" query with a CTE holding the average.
- For each month, find the customer with the highest total, using two CTEs.
- Create
employees, then write a recursive CTE that lists everyone under Ravi with their level. - Take a nested subquery you wrote earlier and rewrite it as three named CTEs. Which is easier to read?
Check yourself
What is a CTE and how long does it last?
A named temporary result defined with WITH; it exists only for the single statement that follows.
How do you define two CTEs in one query?
Write WITH a AS (...), b AS (...) SELECT ..., separating them with a comma; b can use a.
What two parts does a recursive CTE have?
An anchor query that returns the starting rows, and a recursive query joined to the CTE itself, combined with UNION ALL.
Why filter rank results in an outer query?
Window functions cannot be used in WHERE, so compute the rank in a CTE and filter it in the final SELECT.
Interview questions
What is the difference between a CTE and a temporary table?
A CTE exists for one statement and is just a named query. A temporary table is stored for the session, can be indexed and reused by many statements.
When would you use a recursive CTE?
To walk hierarchical data such as employee-manager chains, category trees or bills of materials, where depth is not known in advance.
Is a CTE faster than a subquery?
Not by itself. Modern PostgreSQL treats a simple CTE like a subquery. Use CTEs for readability and measure with EXPLAIN when speed matters.
Summary
WITH name AS (query)gives a step a name; chain CTEs with commas.- CTEs are the usual way to combine aggregation, window functions and filtering cleanly.
- Recursive CTEs use an anchor plus a recursive step to walk hierarchies.
- Choose CTE for readability, temporary table for reuse, view for sharing.
Going deeper
- WITH queries (CTEs): non-recursive, recursive and materialisation options.
- MySQL WITH (common table expressions): MySQL 8 syntax.
- Window functions tutorial: combine with CTEs.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.