ℹ️ Select 'Choose Exercise', or randomize 'Next Random Exercise' in selected language.

Choose Exercise:
Timer 00:00
WPM --
Score --
Acc --
Correct chars --

Recursive CTE for Employee Hierarchy

SQL MariaDB

Goal -- WPM

Ready
Exercise Algorithm Area
1WITH RECURSIVE EmployeeHierarchy AS (

2-- Base case: Select the starting employee

3SELECT

4employee_id,

5manager_id,

6employee_name,

70 AS level

8FROM employees

9WHERE employee_id = 1 -- Starting employee ID

10

11UNION ALL

12

13-- Recursive step: Find direct reports of employees in the previous step

14SELECT

15e.employee_id,

16e.manager_id,

17e.employee_name,

18eh.level + 1

19FROM employees e

20JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id

21)

22SELECT

23employee_id,

24manager_id,

25employee_name,

26level

27FROM EmployeeHierarchy

28ORDER BY level, employee_id;

29

30-- Helper function to validate input (not directly used in CTE but good practice)

31DELIMITER $$

32CREATE FUNCTION ValidateEmployeeExists(emp_id INT) RETURNS BOOLEAN

33BEGIN

34DECLARE count INT;

35SELECT COUNT(*) INTO count FROM employees WHERE employee_id = emp_id;

36RETURN count > 0;

37END$$

38DELIMITER ;

39

40-- Edge case: Handle non-existent starting employee ID

41-- The current query will return no rows if the starting employee_id does not exist.

42-- An alternative would be to check ValidateEmployeeExists before running the CTE.

43

44-- Edge case: Handle circular references (though not explicitly handled here, a depth limit could be added)

45-- For very deep hierarchies, a MAXRECURSION hint might be necessary in some SQL dialects.

46-- MariaDB's default recursion depth is usually sufficient for typical organizational structures.

47

48-- Example Usage:

49-- SELECT * FROM EmployeeHierarchy WHERE employee_id = 1; -- To get the hierarchy for employee 1
Algorithm description viewbox

Recursive CTE for Employee Hierarchy

Algorithm description:

This SQL query uses a recursive Common Table Expression (CTE) to build an organizational hierarchy. It starts with a specific employee and iteratively finds all their direct and indirect reports. This is useful for visualizing reporting structures, calculating seniority, or performing roll-up calculations in an employee database.

Algorithm explanation:

The query defines a recursive CTE named 'EmployeeHierarchy'. The base case selects the initial employee. The recursive step joins the 'employees' table with the 'EmployeeHierarchy' CTE to find employees whose 'manager_id' matches the 'employee_id' from the previous iteration, effectively traversing down the hierarchy. The 'level' column tracks the depth in the hierarchy. Time complexity is O(N) where N is the number of employees in the hierarchy, as each employee is visited once. Space complexity is O(N) to store the intermediate results of the CTE. Edge cases include handling a non-existent starting employee (which results in an empty set) and potential circular references, which are not explicitly handled but could be mitigated with a recursion depth limit.

Pseudocode:

WITH RECURSIVE EmployeeHierarchy AS (
  -- Base Case:
  SELECT employee_id, manager_id, employee_name, level=0
  FROM employees
  WHERE employee_id = start_employee_id

  UNION ALL

  -- Recursive Step:
  SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
  FROM employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy ORDER BY level;