Back to SQL Practice: 331 Real Interview Queries
Q8Question 8 of 9

Recursive CTEs: Hierarchies, Trees, and Sequences

Answer: A recursive CTE references itself with an anchor (base case) + UNION ALL + recursive member, repeating until no new rows are produced. Used for hierarchies, trees, and sequences.

Key Concepts
  • A recursive CTE has two parts: the anchor member (base case) and the recursive member, connected by UNION ALL.
  • The anchor runs once to produce the initial rows (e.g., CEO with no manager). The recursive member runs repeatedly, joining new rows back to the CTE until no new rows are produced.
  • Use for: org chart traversal, category trees, bill of materials, generating number sequences, graph traversal.
  • Always add a termination condition or max recursion limit to prevent infinite loops. SQL Server default is 100 (OPTION MAXRECURSION).
  • The recursive member can only reference the CTE once. You cannot have multiple references to the recursive CTE in the same recursive member.
  • Track the depth/level with a counter column: start at 0 in the anchor, add 1 in the recursive member.

Definition

A recursive CTE references itself with an anchor (base case) + UNION ALL + recursive member, repeating until no new rows are produced. Used for hierarchies, trees, and sequences.
  • 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):

idnamemanager_idlevel_titledepartment_id
1RajeshNULLCEO101
2Priya1VP101
3Amit1VP102
4Sneha2Director101
5Vikram2Director103
6Meena3Senior Manager102
7Kiran3Senior Manager102

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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?
  4. Am I looking for missing data? Use IS NULL (not = NULL).
  5. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. Am I looking for missing data? Use IS NULL (not = NULL).
  4. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. Are there duplicates I need to remove? Use DISTINCT.
  4. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table factorial.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  5. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table departments.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?
  4. Am I looking for missing data? Use IS NULL (not = NULL).
  5. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Start with the root/base case (anchor). Then define the recursive step that joins back to the CTE.
  3. Do I need to combine results from two separate queries? Use UNION (removes duplicates) or UNION ALL (keeps all).

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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.

---

Share
Use ← → arrow keys to navigate