
Dan Patiño
AI Strategy & Innovation at Coderhouse
Data
A SQL CTE (common table expression) is a named, temporary result set that you define with the WITH clause at the top of a query and then use like a table in the rest of that query. CTEs let data analysts break a complicated question into small, readable steps, such as "first calculate revenue per customer, then keep only active customers, then rank them", instead of nesting subqueries three levels deep. They matter because analysis code is read far more often than it is written: by teammates, by reviewers and by you three months later.
This guide explains the syntax, when a CTE beats a subquery, worked examples analysts use every week, and the mistakes to avoid.
CTE syntax in one minute
The basic pattern is a name, the keyword AS and a query in parentheses, followed by the main query that uses it:
WITH monthly_revenue AS ( SELECT customer_id, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY customer_id, DATE_TRUNC('month', order_date) ) SELECT month, AVG(revenue) AS avg_revenue_per_customer FROM monthly_revenue GROUP BY month ORDER BY month;
The CTE monthly_revenue exists only while this statement runs. It is not saved in the database, which makes it different from a view or a table. Most modern engines support CTEs, including PostgreSQL, SQL Server, MySQL from version 8.0, BigQuery, Snowflake and SQLite. The PostgreSQL documentation on WITH queries and Microsoft's reference for common table expressions are good official references.
Date functions vary by engine (for example DATE_TRUNC in PostgreSQL and Snowflake versus DATETRUNC in recent SQL Server versions), so adjust the examples to the database you use.
CTE vs subquery vs temporary table
Subquery: fine for a single, short step. Once you nest two or three, the logic reads inside-out and becomes hard to debug.
CTE: best when a query has several logical steps, when you reuse the same intermediate result more than once in one statement, or when you want the query to read top to bottom like a recipe.
Temporary table: useful when an intermediate result is large and reused across several separate queries in a session, or when you want to index it.
View: the right choice when the same logic should be shared permanently across reports and people.
For most day-to-day analysis, a CTE is the default choice for readability.
Worked examples analysts use every week
1. Filtering on an aggregate in readable steps
Question: which customers spent more than 1,000 in total, and what share of revenue do they represent?
WITH customer_totals AS ( SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id ), big_spenders AS ( SELECT * FROM customer_totals WHERE total_spent > 1000 ) SELECT COUNT(*) AS big_spender_count, SUM(total_spent) / (SELECT SUM(total_spent) FROM customer_totals) AS revenue_share FROM big_spenders;
Notice how the second CTE refers to the first. Chaining CTEs with commas is what makes multi-step logic easy to follow.
2. Combining CTEs with window functions
Question: what was each customer's most recent order?
WITH ranked_orders AS ( SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders ) SELECT customer_id, order_id, order_date FROM ranked_orders WHERE rn = 1;
You cannot filter on a window function in the same WHERE clause that computes it, so wrapping it in a CTE is the standard pattern. If window functions are new to you, read our guide to SQL window functions and why analysts use them.
3. Building a simple funnel
WITH visitors AS ( SELECT DISTINCT user_id FROM events WHERE event = 'visit' ), signups AS ( SELECT DISTINCT user_id FROM events WHERE event = 'signup' ), buyers AS ( SELECT DISTINCT user_id FROM events WHERE event = 'purchase' ) SELECT (SELECT COUNT(*) FROM visitors) AS visitors, (SELECT COUNT(*) FROM signups) AS signups, (SELECT COUNT(*) FROM buyers) AS buyers;
Each stage has a clear name, so a stakeholder reading the query can follow what is being counted. In a real analysis you would also join the stages on user_id so that only users who completed earlier steps count toward later ones; our guide to SQL joins with examples covers that part.
4. Recursive CTEs for hierarchies
A recursive CTE refers to itself, which lets you walk a hierarchy such as an org chart or a category tree:
WITH RECURSIVE reports AS ( SELECT employee_id, manager_id, name, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.manager_id, e.name, r.level + 1 FROM employees e JOIN reports r ON e.manager_id = r.employee_id ) SELECT * FROM reports ORDER BY level;
The first part is the anchor (the top of the tree) and the second part repeats until no new rows appear. PostgreSQL, MySQL and SQLite use WITH RECURSIVE; SQL Server uses plain WITH. Recursive CTEs are also handy for generating a continuous series of dates when your data has gaps.
Good habits when writing CTEs
Name CTEs after what they contain, such as
active_customersororders_last_90_days, nott1ortemp.One logical step per CTE. If you cannot describe a CTE in one short sentence, split it.
Test step by step. Temporarily end the query with
SELECT * FROM step_name LIMIT 20to check each intermediate result before moving on.Check row counts after joins inside CTEs, since duplicated rows silently inflate totals later in the chain.
Know your engine's performance behavior. Since PostgreSQL 12, simple CTEs can be inlined by the planner, and you can force or prevent that with
MATERIALIZEDorNOT MATERIALIZED. Other engines handle this differently, so check the query plan when performance matters.
CTEs in data analyst interviews
SQL screens frequently include multi-step questions: top N per group, retention by cohort, month-over-month growth or the latest record per user. Interviewers often care as much about clarity as about correctness, and a well-named chain of CTEs makes your reasoning visible. Practicing these patterns on real datasets, and explaining each step out loud, is one of the fastest ways to get comfortable.
FAQ
Are CTEs faster than subqueries?
Not inherently. In many engines they produce the same execution plan. Their main benefit is readability; measure with the query plan if speed matters.
Can I use a CTE in more than one query?
No. A CTE lives only within the statement that defines it. For reuse across queries, use a view or a temporary table.
Can I update or delete data with a CTE?
Many engines allow a CTE before UPDATE or DELETE statements, but as an analyst you will mostly use them for read-only analysis.
Keep learning with Coderhouse
Our Data Analytics Course is an 11-week live program with one class per week and about 6 to 8 hours of practice weekly. It includes SQL foundations and SQL for real analysis, Python and pandas, visualization, statistics and experimentation, and ends with a capstone. If you want to apply the same data skills to building LLM-powered systems, the AI Engineering Course is designed for developers comfortable with Python.
I'm Dan Patiño, head of AI Strategy & Innovation at Coderhouse. My day-to-day work involves merging the tactical management of e-commerce (CRO, Email Marketing and SEO) with the development of disruptive solutions. I specialize in building internal AI-powered apps to automate tasks and boost innovation within the team. I firmly believe that technology is strategy's best ally. To dive deeper into my professional journey, I'll be waiting for you on my LinkedIn profile.