SQL Recursive CTE Interview Problem: Find All Managers of an Employee Up to the CEO
Hierarchical data is everywhere in enterprise systems. Org structures, Category trees, Folder hierarchies, Bill of Materials, and Reporting chains are all examples of parent-child relationship
A common SQL interview question is:
Given an Employee ID, list all managers of that employee up to the highest level in the organisation.
In this article, we’ll solve this problem using a Recursive Common Table Expression (Recursive CTE).
Video Explanation:
Sample Employee Data
Consider the following employee table:
Here:
emp_id= Employee IDmanager_id= Direct Manager’s Employee IDNULLindicates the top-most manager (CEO)
Organisation Hierarchy
The employee data forms the following hierarchy:
Tim (6)
│
└── Radha (7)
│
├── Hari (8)
│ │
│ └── Shyam (9)
│ │
│ └── Ram (10)
│
├── John (11)
│
└── Mike (12)
Problem Statement
For a given employee, find all managers up to the highest level.
Suppose the input employee is:
Employee ID = 10
Employee Name = Ram
Expected reporting chain:
Ram → Shyam → Hari → Radha → Tim
Expected Output
Where:
Level 0 = Employee
Level 1 = Direct Manager
Level 2 = Manager’s Manager
And so on...
Visual Representation of Output
Ram (10)
│
└── Shyam (9)
│
└── Hari (8)
│
└── Radha (7)
│
└── Tim (6)
Solution Using Recursive CTE
WITH RECURSIVE manager_hierarchy AS (
-- Anchor Query
SELECT
0 AS level,
emp_id,
emp_name,
manager_id
FROM employee
WHERE emp_id = 10
UNION ALL
-- Recursive Query
SELECT
mh.level + 1,
e.emp_id,
e.emp_name,
e.manager_id
FROM employee e
JOIN manager_hierarchy mh
ON e.emp_id = mh.manager_id
)
SELECT *
FROM manager_hierarchy
ORDER BY level;
Understanding the Logic
The recursive CTE consists of two parts:
1. Anchor Query
The anchor query identifies the starting employee.
SELECT
emp_id,
emp_name,
manager_id,
0 AS level
FROM employee
WHERE emp_id = 10
Result:
2. Recursive Query
The recursive query keeps moving upward in the hierarchy.
SELECT
e.emp_id,
e.emp_name,
e.manager_id,
mh.level + 1
FROM employee e
JOIN manager_hierarchy mh
ON e.emp_id = mh.manager_id
At every iteration:
Find the manager of the current employee.
Add the manager to the result.
Repeat until a row with
manager_id IS NULLis reached.
Recursive Execution Walkthrough
Iteration 1
Current Employee:
Manager Found:
Iteration 2
Current Employee:
Manager Found:
Iteration 3
Current Employee:
Manager Found:
Iteration 4
Current Employee:
Manager Found:
Iteration 5
Current Employee:
Manager Found:
No rows found
Recursion stops.
Final Output
Complexity Analysis
Let:
N = Number of levels in the hierarchy
The recursive query visits each level exactly once.
Time Complexity
O(N)
Space Complexity
O(N)
Real-World Applications
This pattern is frequently used for:
Organisation reporting structures
Employee-manager hierarchy
Folder and directory traversal
Product category trees
Bill of Materials (BOM)
Approval workflow chains
Multi-level referral systems
Role inheritance structures
Key Interview Takeaways
When asked to find all managers of an employee:
Start from the employee (Anchor Query).
Move upward using
manager_id.Continue until the highest manager (
NULL) is reached.Use a Recursive CTE to avoid writing multiple self-joins.
The recursion naturally handles hierarchies of any depth.
Recursive CTEs are one of the most elegant SQL features for solving hierarchical data problems and are frequently asked in Data Engineer, Data Architect, and Big Data interviews.
Try Yourself..
Here is the query that you can use to generate the employee data.
with recursive employee as (
select 10 as emp_id, ‘Ram’ as emp_name, 9 as manager_id
union all
select 9 as emp_id, ‘Shyam’ as emp_name, 8 as manager_id
union all
select 8 as emp_id, ‘Hari’ as emp_name, 7 as manager_id
union all
select 7 as emp_id, ‘Radha’ as emp_name, 6 as manager_id
union all
select 11 as emp_id, ‘John’ as emp_name, 7 as manager_id
union all
select 12 as emp_id, ‘Mike’ as emp_name, 7 as manager_id
union all
select 6 as emp_id, ‘Tim’ as emp_name, null as manager_id
)
select * from employee
Problem Statement: Get the required output from input data.
Input:
Output:
