sqladvanced15 minutes

Build a Recursive SQL Function to Calculate Employee Management Hierarchy Depth

Create a user-defined SQL function that calculates the depth of management hierarchy for a given employee in an organizational chart stored in a single table. This advanced task requires knowledge of recursive Common Table Expressions (CTEs) and efficient query design.

Challenge prompt

Given a table Employees(employee_id INT PRIMARY KEY, manager_id INT), write a SQL function get_management_depth(employee_id INT) RETURNS INT that returns the number of management levels above the given employee. The top-level manager (who has NULL as a manager_id) should have a depth of 0. For example, if employee 10 reports to 7 who reports to 3 (who is top-level), then the depth of employee 10 is 2.

Guidance

• Use a recursive CTE to traverse the hierarchy from the employee up to the top manager.
• Ensure your function handles cases where an employee has no manager (depth 0).
• Optimize to avoid infinite recursion if there are cycles in the data.
• Test your function with various employee_ids to verify correctness.

Hints

• Use a base case where the manager_id is NULL (top-level).
• The recursive part should repeatedly select the manager until reaching the top.
• Use COUNT or a row numbering function within the CTE to calculate the depth.

Starter code

CREATE FUNCTION get_management_depth(emp_id INT) RETURNS INT AS $$
WITH RECURSIVE management_chain AS (
  SELECT employee_id, manager_id, 0 AS depth
  FROM Employees
  WHERE employee_id = emp_id
  UNION ALL
  SELECT e.employee_id, e.manager_id, mc.depth + 1
  FROM Employees e
  JOIN management_chain mc ON e.employee_id = mc.manager_id
  WHERE e.manager_id IS NOT NULL
)
SELECT MAX(depth) FROM management_chain;
$$ LANGUAGE SQL;

Expected output

For example, get_management_depth(10) returns 2 if 10 reports -> 7 reports -> 3 (top-level).

Core concepts

recursive CTEuser-defined functionshierarchical queriesSQL optimization

Challenge a Friend

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