Traverse Organizational Hierarchy with Recursive CTEs in SQL

Question

Given `employees` (emp_id, name, manager_id), write a recursive CTE to output each employee, their management depth level (CEO = 1, their direct reports = 2, etc.), and their full breadcrumb reporting path.

Accepted Answer

Optimal Solution:
WITH RECURSIVE OrgTree AS (
  -- Anchor member: Top executive (no manager)
  SELECT 
    emp_id, 
    name, 
    1 AS level, 
    CAST(name AS TEXT) AS path
  FROM employees
  WHERE manager_id IS NULL

UNION ALL

-- Recursive member: Subordinates reporting to employees in OrgTree
  SELECT 
    e.emp_id, 
    e.name, 
    ot.level + 1 AS level, 
    ot.path || ' -> ' || e.name AS path
  FROM employees e
  JOIN OrgTree ot ON e.manager_id = ot.emp_id
)
SELECT emp_id, name, level, path
FROM OrgTree
ORDER BY level, emp_id;

Technical Explanation:
The anchor query finds the root node (`manager_id IS NULL`). The recursive member joins the anchor with the employee table along `e.manager_id = ot.emp_id`, incrementing `level` by 1 at each iteration until all descendants are traversed.

emp_id	name	level	path
1	Rajesh	1	Rajesh
2	Kaushal	2	Rajesh -> Kaushal
3	Ankit	3	Rajesh -> Kaushal -> Ankit