CTE in SQL: How to Use WITH Clauses and Recursive CTEs (with Examples)

A CTE in SQL (common table expression) is a named, temporary result set you define with the WITH keyword at the start of a query and then use like a table. CTEs do not add new capability so much as clarity: they let you break a long query into readable steps, reuse a step without repeating it, and, with WITH RECURSIVE, walk hierarchies and generate series. They also appear in a large share of SQL interview questions for data analysts.

The sample data

The examples use three small tables: customers (4 rows), orders (6 rows with an order date and amount) and employees (6 rows with a department, salary and manager). They are the same tables used in our guide to joins in SQL, so you can paste both guides into the free SQL compiler and run everything in your browser.

Basic CTE syntax

Here is a CTE that totals revenue per customer, then a main query that joins it to customer names and keeps customers above ₹1,000.

WITH customer_revenue AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
JOIN customers c ON c.customer_id = cr.customer_id
WHERE cr.revenue > 1000
ORDER BY cr.revenue DESC;

The same result can be written with a subquery in the FROM clause. The CTE version reads top to bottom: first compute revenue, then use it. That ordering is the main reason analysts prefer CTEs for anything longer than a few lines.

Chaining several CTEs

Separate multiple CTEs with commas. Each one can read the ones defined before it. This example calculates monthly revenue, attaches the previous month with a window function, and then computes month-over-month growth.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
with_prev AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly
)
SELECT month, revenue, prev_revenue,
       ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS mom_growth_pct
FROM with_prev
ORDER BY month;

Each step has one job, so when a number looks wrong you can run SELECT * FROM monthly on its own and check it before moving on. Debugging a CTE chain step by step is a habit interviewers notice.

CTEs with window functions: top earner per department

Window functions cannot be filtered in the same SELECT where they are computed, so a CTE (or subquery) is the standard way to rank and then filter.

WITH ranked AS (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT dept, name, salary
FROM ranked
WHERE rnk = 1
ORDER BY dept;

Change rnk = 1 to rnk <= 3 for the classic "top 3 per group" question. DENSE_RANK keeps ties; use ROW_NUMBER if you need exactly one row per group.

Recursive CTEs

A recursive CTE has two parts joined by UNION ALL: an anchor query that runs once, and a recursive query that refers to the CTE itself and runs until it returns no new rows. Always include a condition that stops the recursion.

Generating a date series

WITH RECURSIVE days AS (
  SELECT DATE '2026-03-01' AS day
  UNION ALL
  SELECT (day + 1)::date FROM days WHERE day < DATE '2026-03-05'
)
SELECT day FROM days;

A full calendar like this is useful for LEFT JOINing daily sales so that days with zero sales still appear in a report. PostgreSQL also has a built-in generate_series function for dates, but the recursive pattern works across most databases and is what interviewers usually ask for.

Walking an org chart

WITH RECURSIVE reports AS (
  SELECT emp_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, r.level + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.emp_id
)
SELECT name, level
FROM reports
ORDER BY level, name;

The anchor finds the person with no manager; each recursive pass finds the people who report to the previous level.

CTE vs subquery vs temporary table

In PostgreSQL 12 and later, a CTE that is referenced once is usually inlined by the planner, so performance is similar to the equivalent subquery. You can force the older behavior with AS MATERIALIZED when you want the step computed once.

Common CTE mistakes

• Forgetting the comma between CTEs, or adding one before the main SELECT.

• Expecting a CTE to exist in the next query. It only belongs to the statement it is written in.

• Writing a recursive CTE without a stopping condition, which runs until the database stops it.

• Using UNION instead of UNION ALL in a recursive CTE without meaning to; UNION removes duplicates at every pass, which changes results and costs time.

Frequently asked questions

What is a CTE in SQL in simple words?

It is a named temporary result set written with WITH at the start of a query. You can then select from it like a table for the rest of that statement.

Are CTEs faster than subqueries?

Not inherently. In modern PostgreSQL the planner usually treats a simple CTE like a subquery. Choose CTEs for readability, and check the query plan if performance matters.

Can I use more than one CTE in a query?

Yes. Separate them with commas after a single WITH. Later CTEs can refer to earlier ones.

When should I use a recursive CTE?

When each step depends on the previous one: hierarchies such as org charts or category trees, bill-of-materials structures, and generated sequences such as date ranges.

How do I practice CTE questions for interviews?

Rewrite your existing subqueries as CTEs, then solve the CTE and recursive CTE problems in our free SQL interview questions set, which checks answers instantly in a browser Postgres.

Recommended internal links

• Joins in SQL guide: https://skillancy.in/blog/joins-in-sql

• Free online SQL compiler: https://skillancy.in/tools/sql-compiler

• 25 SQL interview questions with a live editor: https://skillancy.in/practice/sql-questions

• Data Analytics with Agentic AI program: https://skillancy.in/courses/data-analytics-with-agentic-ai

Sources and publishing references

PostgreSQL documentation: WITH queries (common table expressions): https://www.postgresql.org/docs/current/queries-with.html

All query outputs on this page were produced by running the queries against PostgreSQL.