Recursive CTE for hierarchical data in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Recursive CTE for Employee Hierarchy

Given the following table Employees with columns id, name, and manager_id, what is the output of this recursive CTE query?

WITH RECURSIVE EmployeeHierarchy 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, eh.level + 1
  FROM Employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, level FROM EmployeeHierarchy ORDER BY level, id;

SQL

WITH RECURSIVE EmployeeHierarchy 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, eh.level + 1
  FROM Employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, level FROM EmployeeHierarchy ORDER BY level, id;

A [{id: 1, name: 'Alice', level: 1}, {id: 2, name: 'Bob', level: 2}, {id: 3, name: 'Charlie', level: 2}, {id: 4, name: 'Diana', level: 3}]

B [{id: 1, name: 'Alice', level: 1}, {id: 2, name: 'Bob', level: 1}, {id: 3, name: 'Charlie', level: 1}, {id: 4, name: 'Diana', level: 1}]

C [{id: 4, name: 'Diana', level: 3}, {id: 3, name: 'Charlie', level: 2}, {id: 2, name: 'Bob', level: 2}, {id: 1, name: 'Alice', level: 1}]

D [{id: 1, name: 'Alice', level: 1}, {id: 3, name: 'Charlie', level: 2}, {id: 2, name: 'Bob', level: 2}, {id: 4, name: 'Diana', level: 3}]

💡 Hint

Think about how the recursion starts from the top-level manager and adds employees level by level.

The recursive CTE starts with employees who have no manager (level 1). Then it adds employees whose manager is in the previous level, increasing the level by 1 each time. The output is ordered by level and id, so Alice (id 1) is level 1, Bob and Charlie (ids 2 and 3) are level 2, and Diana (id 4) is level 3.

📝 Syntax

intermediate

Identify the Syntax Error in Recursive CTE

Which option contains a syntax error in the recursive CTE definition for hierarchical data?

SQL

WITH RECURSIVE EmployeeTree 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, depth + 1
  FROM Employees e
  JOIN EmployeeTree et ON e.manager_id = et.id
)
SELECT * FROM EmployeeTree;

A The recursive part uses 'depth + 1' without table alias, causing ambiguity.

B The base query is missing a WHERE clause to filter top-level managers.

C The recursive CTE is missing the RECURSIVE keyword after WITH.

D The JOIN condition uses 'e.manager_id = et.id' which is invalid.

💡 Hint

Check if all columns in the recursive part are properly qualified with table aliases.

In the recursive SELECT, 'depth + 1' is ambiguous because 'depth' is not prefixed with the alias 'et'. It should be 'et.depth + 1' to refer to the previous level's depth.

❓ optimization

advanced

Optimize Recursive CTE to Avoid Duplicate Rows

You have a recursive CTE that sometimes returns duplicate rows in the hierarchy. Which option best optimizes the query to avoid duplicates?

SQL

WITH RECURSIVE OrgChart 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, oc.level + 1
  FROM Employees e
  JOIN OrgChart oc ON e.manager_id = oc.id
)
SELECT * FROM OrgChart;

A Add DISTINCT in the final SELECT: SELECT DISTINCT * FROM OrgChart;

B Add a WHERE clause in the recursive part to exclude already visited ids.

C Use a CTE column to track visited ids and filter duplicates in the recursive step.

D Replace JOIN with LEFT JOIN in the recursive part to avoid duplicates.

💡 Hint

Think about how to track which rows have been processed to prevent revisiting them.

Tracking visited ids in a column (like a path or set) and filtering them in the recursive step prevents duplicates by ensuring each node is processed once.

🧠 Conceptual

advanced

Understanding Recursive CTE Termination Condition

What ensures that a recursive CTE for hierarchical data eventually stops and does not run infinitely?

A The RECURSIVE keyword automatically limits recursion depth.

B The base query selects rows with NULL manager_id, providing a starting point.

C The recursive part must join on a condition that reduces the number of rows each iteration.

D The recursion stops when no new rows are returned by the recursive SELECT.

💡 Hint

Consider what happens when the recursive SELECT returns zero rows.

A recursive CTE stops when the recursive SELECT returns no new rows to add. This natural termination prevents infinite loops.

🔧 Debug

expert

Debugging Unexpected Output in Recursive CTE

A recursive CTE for a category tree returns fewer rows than expected. Which option is the most likely cause?

SQL

WITH RECURSIVE CategoryTree AS (
  SELECT id, name, parent_id
  FROM Categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM Categories c
  JOIN CategoryTree ct ON c.parent_id = ct.id
  WHERE c.is_active = TRUE
)
SELECT * FROM CategoryTree;

A The JOIN condition 'c.parent_id = ct.id' is incorrect and causes missing rows.

B The WHERE clause 'c.is_active = TRUE' in the recursive part filters out some categories, reducing rows.

C The base query should include 'c.is_active = TRUE' to match the recursive part.

D The recursive CTE is missing the RECURSIVE keyword.

💡 Hint

Check filters in the recursive part that might exclude rows.

Filtering with 'c.is_active = TRUE' in the recursive part excludes inactive categories and their descendants, causing fewer rows than expected.