Dev Nest Guide
sqladvanced15 minutes

Fix the Performance Bug in Recursive Employee Reporting Hierarchy Query

You are given a recursive CTE query designed to retrieve the full reporting hierarchy of employees in an organization, but the query runs very slowly and returns incorrect results with duplicated rows. Your task is to identify and fix the bug(s) in the query to ensure it runs efficiently and returns the correct hierarchical employee reporting structure without duplicates.

Challenge prompt

You have a table `Employees` with columns `EmployeeID`, `ManagerID`, and `EmployeeName`. The goal is to write a recursive SQL query that returns each employee along with their manager's chain using a recursive CTE. The provided query is producing excessive duplicate rows and is very slow. Fix the query so it optimally retrieves the full hierarchy for all employees without duplicates or infinite recursion.

Guidance

  • • Check the recursive CTE anchor member and recursive member conditions carefully for correctness.
  • • Ensure the recursive part correctly joins back to the base table without causing cross joins or duplicates.
  • • Consider ways to prevent infinite recursion or cycles in the hierarchy.

Hints

  • • Make sure the recursion predicate uses the correct join condition between CTE and Employees.
  • • Use DISTINCT or proper filtering to avoid duplicates if needed, but aim to eliminate duplicates through the join logic.
  • • Consider limiting the recursion depth or adding cycle detection columns if your dialect supports them.

Starter code

WITH EmployeeHierarchy AS (
  SELECT EmployeeID, ManagerID, EmployeeName, CAST(EmployeeName AS VARCHAR(MAX)) AS FullPath
  FROM Employees
  WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, eh.FullPath + ' > ' + e.EmployeeName
  FROM Employees e
  JOIN EmployeeHierarchy eh ON e.EmployeeID = eh.ManagerID
)
SELECT * FROM EmployeeHierarchy;

Expected output

A list of employees with their full management chain in the 'FullPath' column, with no duplicates and complete hierarchy, for example: EmployeeID | ManagerID | EmployeeName | FullPath --------------------------------------------------- 1 | NULL | Alice | Alice 2 | 1 | Bob | Alice > Bob 3 | 2 | Carol | Alice > Bob > Carol

Core concepts

recursive CTEhierarchical queriesSQL joinsquery optimization

Challenge a Friend

Send this duel to someone else and see if they can solve it.