Definition
- Anchor member: The base case. Runs once. Example: SELECT * FROM employees WHERE manager_id IS NULL.
- Recursive member: Joins back to the CTE. Runs repeatedly until empty.
- UNION ALL: Connects anchor and recursive member. UNION (without ALL) is not allowed.
- Termination: Stops when recursive member returns no rows. Add MAXRECURSION for safety.
- Depth tracking: Add a level column starting at 0, incrementing by 1.
- Use cases: Org charts, category trees, bill of materials, number generation.
This section is a focused tutorial on Recursive CTEs. Each question teaches you to think through the problem before writing SQL, build the query step by step, understand how the database executes it, and optimize for performance.
Understanding the Concepts
## Recursive CTEs: Hierarchies, Trees, and Sequences
A recursive CTE references itself. It has an anchor member (base case) and a recursive member connected by UNION ALL. The anchor selects the starting rows (e.g., CEO). The recursive member joins back to the CTE to find the next level (direct reports). Execution repeats until no new rows are produced. Use for org charts, category trees, bill of materials, and generating number sequences.
Sample Tables
employees hierarchy table (shared across all queries in this section):
| id | name | manager_id | level_title | department_id |
|---|---|---|---|---|
| 1 | Rajesh | NULL | CEO | 101 |
| 2 | Priya | 1 | VP | 101 |
| 3 | Amit | 1 | VP | 102 |
| 4 | Sneha | 2 | Director | 101 |
| 5 | Vikram | 2 | Director | 103 |
| 6 | Meena | 3 | Senior Manager | 102 |
| 7 | Kiran | 3 | Senior Manager | 102 |
This table forms a tree: Rajesh is CEO (no manager). Priya and Amit report to Rajesh. Sneha and Vikram report to Priya. Meena and Kiran report to Amit.
---
Q1. Recursive query to find the full reporting chain for each employee
Problem: Recursive query to find the full reporting chain for each employee.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
- Am I looking for missing data? Use IS NULL (not = NULL).
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE reporting_chain AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, rc.level + 1
FROM employees e
JOIN reporting_chain rc ON e.manager_id = rc.id
)
SELECT * FROM reporting_chain ORDER BY level,
id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q2. Detect hierarchical depth of each employee in the org chart
Problem: Detect hierarchical depth of each employee in the org chart.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- Am I looking for missing data? Use IS NULL (not = NULL).
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE employee_depth AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ed.depth + 1
FROM employees e
JOIN employee_depth ed ON e.manager_id = ed.id
)
SELECT * FROM employee_depth;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q3. Write a query to detect circular references in employee-manager hierarchy (cycles)
Problem: Write a query to detect circular references in employee-manager hierarchy (cycles).
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- Are there duplicates I need to remove? Use DISTINCT.
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE mgr_path (id, manager_id, path)
AS (
SELECT id, manager_id, ARRAY[id]
FROM employees
WHERE manager_id IS NOT NULL
UNION ALL
SELECT e.id, e.manager_id, path || e.id
FROM employees e
JOIN mgr_path mp ON e.manager_id = mp.id
WHERE NOT e.id = ANY(path)
)
SELECT DISTINCT id
FROM mgr_path
WHERE id = ANY(path);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q4. Write a recursive query to calculate factorial of a number (e.g., 5)
Problem: Write a recursive query to calculate factorial of a number (e.g., 5).
How to think before writing:
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE factorial(n, fact) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, fact * (n + 1)
FROM factorial
WHERE n < 5
)
SELECT fact FROM factorial WHERE n = 5;
Step-by-step how the database executes this:
- FROM: Reads table
factorial. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q5. Write a query to get employees who reported directly or indirectly to a given manager (hierarchy traversal)
Problem: Write a query to get employees who reported directly or indirectly to a given manager (hierarchy traversal).
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE reporting AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id = 101 -- replace 101 with
manager's id
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN reporting r ON e.manager_id = r.id
)
SELECT * FROM reporting;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q6. Write a recursive query to list all ancestors (managers) of a given employee
Problem: Write a recursive query to list all ancestors (managers) of a given employee.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE ancestors AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 123 -- given employee id
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN ancestors a ON e.id = a.manager_id
)
SELECT * FROM ancestors WHERE id != 123;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q7. Write a recursive query to list all descendants of a manager in an organizational hierarchy
Problem: Write a recursive query to list all descendants of a manager in an organizational hierarchy.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE descendants AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id = 100 -- starting manager id
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN descendants d ON e.manager_id = d.id
)
SELECT * FROM descendants;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q8. Write a recursive query to compute the total budget under each manager (including subordinates)
Problem: Write a recursive query to compute the total budget under each manager (including subordinates).
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE manager_budget AS (
SELECT id, manager_id, budget
FROM departments
UNION ALL
SELECT d.id, d.manager_id, mb.budget
FROM departments d
JOIN manager_budget mb ON d.manager_id = mb.id
)
SELECT manager_id, SUM(budget) AS total_budget
FROM manager_budget
GROUP BY manager_id;
Step-by-step how the database executes this:
- FROM: Reads table
departments. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q9. Write a recursive query to find all employees and their level of reporting (distance from CEO)
Problem: Write a recursive query to find all employees and their level of reporting (distance from CEO).
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
- Am I looking for missing data? Use IS NULL (not = NULL).
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE hierarchy AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL -- CEO level
UNION ALL
SELECT e.id, e.name, e.manager_id, h.level + 1
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy ORDER BY level,
manager_id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q10. Write a recursive query to get all descendants of a manager
Problem: Write a recursive query to get all descendants of a manager.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
- Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id
FROM employees
WHERE id = :manager_id
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates WHERE id !=
:manager_id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Key Takeaway
This section covered 10 interview questions on Recursive CTEs: Hierarchies, Trees, and Sequences. Each question followed the same learning pattern: understand the problem, think about the approach, write a pseudo query in plain English, translate to SQL, understand the execution order, and consider optimization.
A recursive CTE references itself. It has an anchor member (base case) and a recursive member connected by UNION ALL. The anchor selects the starting rows (e.g., CEO). The recursive member joins back to the CTE to find the next level (direct reports). Execution repeats until no new rows are produced. Use for org charts, category trees, bill of materials, and generating number sequences.
Practice each query on the shared sample table. If you can solve all 10 without looking at the answer, you have mastered this concept.
---