Employee Hierarchy SQL Interview Questions

 

Employee Hierarchy SQL Interview Questions

Employee hierarchy questions are among the most common SQL interview topics.

Sample Employee Table

EmpID	EmpName	ManagerID
1	CEO	NULL
2	VP Sales	1
3	VP IT	1
4	Manager A	2
5	Manager B	2
6	Team Lead X	4
7	Team Lead Y	4
8	Developer 1	6
9	Developer 2	6

Hierarchy:

CEO
├── VP Sales
│   ├── Manager A
│   │   ├── Team Lead X
│   │   │   ├── Developer 1
│   │   │   └── Developer 2
│   │   └── Team Lead Y
│   └── Manager B
└── VP IT

---

1. Show Employee with Manager Name

SELECT e.EmpName Employee,
       m.EmpName Manager
FROM Employee e
LEFT JOIN Employee m
ON e.ManagerID = m.EmpID;

Output:

Employee	Manager
VP Sales	CEO
Manager A	VP Sales

---

2. Find CEO (Top-Level Employee)

SELECT *
FROM Employee
WHERE ManagerID IS NULL;

---

3. Find Direct Reports of CEO

SELECT *
FROM Employee
WHERE ManagerID = 1;

Result:

VP Sales
VP IT

---

4. Count Employees Reporting to Each Manager

SELECT ManagerID,
       COUNT(*) EmployeeCount
FROM Employee
GROUP BY ManagerID;

---

5. Find Employees with No Subordinates

SELECT *
FROM Employee e
WHERE NOT EXISTS
(
    SELECT 1
    FROM Employee x
    WHERE x.ManagerID=e.EmpID
);

Result:

Developer1
Developer2
Team Lead Y
Manager B

---

6. Find Managers Having More Than 3 Employees

SELECT ManagerID,
       COUNT(*)
FROM Employee
GROUP BY ManagerID
HAVING COUNT(*) > 3;

---

7. Find Level of Each Employee (Recursive CTE)

SQL Server

WITH EmpHierarchy AS
(
    SELECT EmpID,
           EmpName,
           ManagerID,
           1 AS Level
    FROM Employee
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmpID,
           e.EmpName,
           e.ManagerID,
           h.Level + 1
    FROM Employee e
    JOIN EmpHierarchy h
    ON e.ManagerID = h.EmpID
)
SELECT *
FROM EmpHierarchy;

Output:

Employee	Level
CEO	1
VP Sales	2
Manager A	3
Team Lead X	4
Developer1	5

---

8. Find All Employees Under Manager A

WITH Hierarchy AS
(
    SELECT EmpID,
           EmpName,
           ManagerID
    FROM Employee
    WHERE EmpID = 4

    UNION ALL

    SELECT e.*
    FROM Employee e
    JOIN Hierarchy h
    ON e.ManagerID=h.EmpID
)
SELECT *
FROM Hierarchy;

Returns:

Manager A
Team Lead X
Team Lead Y
Developer1
Developer2

---

9. Find Total Team Size Under Each Manager

Recursive hierarchy + aggregation.

Often asked in architect interviews.

Manager A = 5
VP Sales = 7
CEO = 8

---

10. Find Chain of Command for Developer1

Developer1
→ Team Lead X
→ Manager A
→ VP Sales
→ CEO

Recursive CTE upward traversal.

---

11. Find Maximum Hierarchy Depth

WITH Hierarchy AS
(
    SELECT EmpID,
           ManagerID,
           1 Level
    FROM Employee
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmpID,
           e.ManagerID,
           h.Level+1
    FROM Employee e
    JOIN Hierarchy h
    ON e.ManagerID=h.EmpID
)
SELECT MAX(Level)
FROM Hierarchy;

Answer:

5

---

12. Find Managers Who Manage Managers

SELECT DISTINCT m.EmpName
FROM Employee e
JOIN Employee m
ON e.ManagerID=m.EmpID
WHERE EXISTS
(
    SELECT 1
    FROM Employee x
    WHERE x.ManagerID=e.EmpID
);

Result:

CEO
VP Sales
Manager A

---

13. Find Employees at Same Level

Example:

Level 2:
VP Sales
VP IT

Use recursive CTE with level calculation.

---

14. Find Lowest-Level Employees (Leaf Nodes)

SELECT *
FROM Employee e
WHERE NOT EXISTS
(
SELECT 1
FROM Employee x
WHERE x.ManagerID=e.EmpID
);

---

15. Interview Favorite Question

How many employees report to CEO directly or indirectly?

WITH Hierarchy AS
(
    SELECT EmpID
    FROM Employee
    WHERE EmpID=1

    UNION ALL

    SELECT e.EmpID
    FROM Employee e
    JOIN Hierarchy h
    ON e.ManagerID=h.EmpID
)
SELECT COUNT(*)-1
FROM Hierarchy;

Answer:

8 Employees

---

Employee Hierarchy Questions Asked Most Often

1. Employee ↔ Manager Self Join
2. CEO Identification
3. Direct Reports
4. Employees with No Subordinates
5. Recursive CTE
6. Hierarchy Levels
7. Team Size Calculation
8. Chain of Command
9. Organization Tree
10. Total Reports (Direct + Indirect)

These are very common in SQL Server, Snowflake, Oracle, and BI Architect interviews because they test joins, recursion, CTEs, and real-world organizational reporting structures.