SQL CTE and Recursive Query Interview Questions
Last updated 2026-11-19
A common table expression (CTE) is a named, temporary result set defined with WITH and used in the query that follows. Interviewers ask about CTEs to see whether you can structure a long query into readable steps, and about recursive CTEs to see whether you can walk a hierarchy such as an employee-manager tree or generate a series of rows.
The employees table below has a manager_id column forming a tree: Asha manages Bilal, Chitra, Esha and Farid, Chitra manages Dev, and Esha manages Gita. All the queries were run on it and the results are real. Syntax differences: PostgreSQL, MySQL 8 and SQLite need the keyword RECURSIVE, SQL Server and Oracle do not. For related topics see SQL subqueries and SQL window functions.
| Syntax | WITH [RECURSIVE] name AS (query) SELECT ... |
|---|---|
| Supported | PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite |
| Sample | employee-manager hierarchy |
Sample data
The manager_id column points at emp_id of the manager. Asha has no manager and is the root of the tree.
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT, manager_id INT);
INSERT INTO departments VALUES (10,'Sales'),(20,'Engineering'),(30,'HR'),(40,'Legal');
INSERT INTO employees VALUES
(1,'Asha',20,90000,NULL),(2,'Bilal',20,75000,1),(3,'Chitra',10,60000,1),
(4,'Dev',10,60000,3),(5,'Esha',30,50000,1),(6,'Farid',20,75000,1),(7,'Gita',NULL,40000,5);How a recursive CTE runs
- The anchor member runs once and produces the starting rows.
- The recursive member joins the CTE's previous output to the table, producing the next level.
- UNION ALL appends each level, and the loop stops when the recursive member returns no rows.
- Without a stopping condition (for example, a cycle in the data) it never ends, so guard with a depth limit.
SQL CTE and Recursive Query Interview Questions: questions with worked answers
1 What is a CTE, and how is it different from a subquery or a view?
easyA CTE is a named result set scoped to one statement. It improves readability, can be referenced several times in the same query and supports recursion, which subqueries cannot. A view is a stored definition that persists in the database and can be reused by many statements. A derived table (subquery in FROM) is anonymous and used once. A CTE is not stored, and whether it is evaluated once or inlined depends on the database.
sqlWITH dept_avg AS (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) SELECT e.name, e.salary, d.avg_sal FROM employees e JOIN dept_avg d ON d.dept_id = e.dept_id WHERE e.salary > d.avg_sal ORDER BY e.emp_id; -- name | salary | avg_sal -- -----+--------+-------- -- Asha | 90000 | 800002 Can you define several CTEs in one statement and reference earlier ones?
mediumYes. Separate them with commas after a single WITH, and later CTEs can read earlier ones. This is how a long report query is broken into steps, each testable on its own. Below, the first CTE totals each department and the second keeps only the larger ones.
sqlWITH totals AS (SELECT dept_id, SUM(salary) AS total FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id), big AS (SELECT dept_id, total FROM totals WHERE total > 100000) SELECT d.dept_name, big.total FROM big JOIN departments d ON d.dept_id = big.dept_id; -- dept_name | total -- ------------+------- -- Sales | 120000 -- Engineering | 2400003 Write a recursive CTE that lists every employee under Asha with their level.
mediumThe anchor selects the root (Asha at level 0), and the recursive member joins employees to the previous level on manager_id = emp_id, adding 1 each time. UNION ALL keeps each level. The recursion ends when the join returns no more rows.
sqlWITH RECURSIVE tree AS ( SELECT emp_id, name, manager_id, 0 AS lvl FROM employees WHERE emp_id = 1 UNION ALL SELECT e.emp_id, e.name, e.manager_id, t.lvl + 1 FROM employees e JOIN tree t ON e.manager_id = t.emp_id ) SELECT name, lvl FROM tree ORDER BY lvl, emp_id; -- name | lvl -- -------+---- -- Asha | 0 -- Bilal | 1 -- Chitra | 1 -- Esha | 1 -- Farid | 1 -- Dev | 2 -- Gita | 24 Show the full management chain as a path string, such as Asha > Esha > Gita.
hardCarry a string column through the recursion and append the current name at each level. String concatenation differs: || in PostgreSQL and SQLite, CONCAT() in MySQL, + in SQL Server. In MySQL the anchor's column type fixes the width, so cast the anchor to a long enough VARCHAR or the append raises "data too long".
sqlWITH RECURSIVE chain AS ( SELECT emp_id, name, name AS path FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, c.path || ' > ' || e.name FROM employees e JOIN chain c ON e.manager_id = c.emp_id ) SELECT path FROM chain ORDER BY path; -- path -- ------------------- -- Asha -- Asha > Bilal -- Asha > Chitra -- Asha > Chitra > Dev -- Asha > Esha -- Asha > Esha > Gita -- Asha > Farid5 Generate the numbers 1 to 5 with a recursive CTE.
mediumAnchor with 1 and recurse by adding 1 while the value is below the limit. This is the standard way to build a number or date series where a database lacks generate_series. PostgreSQL also has generate_series, and MySQL 8 can raise cte_max_recursion_depth (default 1000), above which the query errors.
sqlWITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5) SELECT x FROM n; -- x -- - -- 1 -- 2 -- 3 -- 4 -- 56 Find missing dates in a sales table using a recursive CTE.
hardGenerate every date between the minimum and maximum, then LEFT JOIN to the data and keep rows with no match. Date arithmetic differs by database: date(d, '+1 day') in SQLite, d + INTERVAL 1 DAY in MySQL and PostgreSQL (d + 1 also works on PostgreSQL dates).
sqlCREATE TABLE daily_sales (d DATE, amt INT); INSERT INTO daily_sales VALUES ('2026-04-01',100),('2026-04-02',150),('2026-04-04',80),('2026-04-05',120);sqlWITH RECURSIVE cal(d) AS ( SELECT MIN(d) FROM daily_sales UNION ALL SELECT date(d, '+1 day') FROM cal WHERE d < (SELECT MAX(d) FROM daily_sales) ) SELECT cal.d AS missing_date FROM cal LEFT JOIN daily_sales s ON s.d = cal.d WHERE s.d IS NULL; -- missing_date -- ------------ -- 2026-04-037 How do you stop a recursive CTE from looping forever on cyclic data?
hardIf A reports to B and B reports to A, the recursive member never runs out of rows. Add a depth limit in the recursive WHERE (WHERE lvl < 20), track visited ids in a path and exclude repeats, or use CYCLE detection where supported (PostgreSQL 14+ has a CYCLE clause). MySQL stops at cte_max_recursion_depth and SQL Server at MAXRECURSION (default 100), raising an error. UNION (instead of UNION ALL) removes duplicate rows and can also end a cycle when rows repeat exactly, though it costs a comparison per row.
8 What is the difference between UNION and UNION ALL inside a recursive CTE?
mediumUNION ALL simply appends the next level. UNION removes duplicates against all previous rows, so it can stop a cycle and avoid reprocessing but adds a sort or hash. Most hierarchy queries use UNION ALL because the tree has no duplicates, and it is faster. Some engines accept only UNION ALL in the recursive part (SQL Server), while PostgreSQL and MySQL accept both.
9 Count the total number of people under each manager, direct and indirect.
mediumBuild the closure with a recursive CTE that carries the original manager as the root, then count per root. Asha has six reports in total (four direct and two indirect), Chitra has one and Esha has one.
sqlWITH RECURSIVE reach(root, emp_id) AS ( SELECT manager_id, emp_id FROM employees WHERE manager_id IS NOT NULL UNION ALL SELECT r.root, e.emp_id FROM reach r JOIN employees e ON e.manager_id = r.emp_id ) SELECT m.name, COUNT(*) AS team_size FROM reach r JOIN employees m ON m.emp_id = r.root GROUP BY m.name ORDER BY team_size DESC, m.name; -- name | team_size -- -------+---------- -- Asha | 6 -- Chitra | 1 -- Esha | 110 Does a CTE improve performance? What is MATERIALIZED?
mediumNot by itself: a CTE is mostly about readability. Before PostgreSQL 12 every CTE was an optimisation fence, computed once and materialised, so filters could not be pushed into it. From PostgreSQL 12 a non-recursive CTE referenced once is inlined, and you can force behaviour with AS MATERIALIZED or AS NOT MATERIALIZED. MySQL 8 and SQL Server usually inline CTEs and may evaluate a CTE referenced multiple times more than once, so use a temporary table when you need to compute something expensive once.
11 Can a CTE be used with INSERT, UPDATE or DELETE?
mediumYes in PostgreSQL, which also supports data-modifying CTEs with RETURNING, so you can delete rows and insert them into an archive table in one statement. SQL Server allows CTEs in front of INSERT, UPDATE, DELETE and MERGE. MySQL 8 allows WITH before SELECT, UPDATE and DELETE. A frequent use is deleting duplicates with ROW_NUMBER in a CTE, as in window function questions.
sql-- PostgreSQL: archive and delete in one statement WITH moved AS ( DELETE FROM orders WHERE order_date < '2025-01-01' RETURNING * ) INSERT INTO orders_archive SELECT * FROM moved;12 Compute a cumulative balance per account using a recursive CTE, and say why a window function is better.
hardYou can chain rows with recursion, but SUM() OVER (ORDER BY ...) does the same in one pass, is shorter and is not limited by recursion depth. Mention that recursion shines for hierarchies and graphs of unknown depth, while running totals belong to window functions. Choosing the right tool is the point of the question.
13 Which databases support recursive CTEs and what are the keyword differences?
mediumPostgreSQL 8.4+, MySQL 8.0+, SQLite 3.8.3+, MariaDB 10.2.2+, SQL Server 2005+ and Oracle 11gR2+ all support them. PostgreSQL, MySQL and SQLite require WITH RECURSIVE. SQL Server and Oracle use plain WITH and recognise recursion from the self reference. MySQL 5.7 has no CTEs at all, which matters for older hosted databases and some online judges. Oracle also offers the proprietary CONNECT BY for hierarchies.
Frequently asked questions
Are CTEs slower than subqueries?
Usually the same, because most databases inline them. Only older PostgreSQL versions treated CTEs as a barrier.
What is the maximum recursion depth?
It depends: SQL Server defaults to 100 (MAXRECURSION), MySQL to 1000 (cte_max_recursion_depth), PostgreSQL has no fixed limit and relies on your stop condition.
Can I reference a CTE twice?
Yes, within the same statement. Some engines then evaluate it twice, so materialise it if it is expensive.
How do I practise hierarchy queries?
Build an employee or category table with a parent column and write the path, level and descendant-count queries above on it. More drills are in SQL query practice.
Related guides
More in Programming Language Interview Questions by Language
- SQL GROUP BY and HAVING Interview Questions
- SQL Indexes Interview Questions
- SQL Interview Questions and Answers
- SQL Interview Questions for Freshers
- SQL Joins Interview Questions
- SQL Query Practice: Interview Questions with Solutions
- SQL Subqueries Interview Questions
- SQL Window Functions Interview Questions
- TypeScript Interview Questions and Answers
- .NET Core and .NET 8 Interview Questions
- Advanced Python Interview Questions
- ASP.NET Core MVC Interview Questions