Schema and sample data
| emp_id | name | manager_id |
|---|---|---|
| 1 | Alice Chen | NULL |
| 2 | Ben Ortiz | 1 |
| 3 | Cara Diaz | 1 |
| 4 | Deng Wu | 2 |
| 5 | Ella Moss | 2 |
| 6 | Finn Park | 3 |
| 7 | Gita Rao | 4 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT NULL REFERENCES Employees(emp_id)
);
INSERT INTO Employees (emp_id, name, manager_id) VALUES
(1, 'Alice Chen', NULL),
(2, 'Ben Ortiz', 1),
(3, 'Cara Diaz', 1),
(4, 'Deng Wu', 2),
(5, 'Ella Moss', 2),
(6, 'Finn Park', 3),
(7, 'Gita Rao', 4);The Employees table stores a tree: each row has an emp_id, a name, and a manager_id that points at the emp_id of that person's manager. The CEO sits at the top and has a NULL manager_id.
Using a recursive CTE, return each employee's name along with their level in the hierarchy, where the CEO is level 1, the CEO's direct reports are level 2, and so on. Sort the result by level ascending, then by name ascending.
Expected result
| name | level |
|---|---|
| Alice Chen | 1 |
| Ben Ortiz | 2 |
| Cara Diaz | 2 |
| Deng Wu | 3 |
| Ella Moss | 3 |
| Finn Park | 3 |
| Gita Rao | 4 |
Show hint
A recursive CTE has two halves joined by UNION ALL. Start with an anchor query that selects the CEO (the row where manager_id IS NULL) and hands it a level of 1. Then write a recursive member that joins Employees back to the CTE, matching each employee's manager_id to a parent emp_id already in the result, and adds 1 to the parent's level.
Try to write the query yourself before you open this.
Show solution and explanation
WITH EmployeeHierarchy AS (
-- Anchor member: the CEO has no manager, so it seeds level 1
SELECT emp_id, name, manager_id, 1 AS level
FROM Employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: attach each employee to a manager already in the tree
SELECT e.emp_id, e.name, e.manager_id, eh.level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.emp_id
)
SELECT name, level
FROM EmployeeHierarchy
ORDER BY level, name
OPTION (MAXRECURSION 100);A recursive CTE is built from two SELECTs stitched together with UNION ALL. The first is the anchor member: WHERE manager_id IS NULL finds the one row at the top of the tree (Alice Chen) and labels it level 1. This is the starting point the recursion grows from, so it runs exactly once.
The second SELECT is the recursive member. It joins Employees to the CTE itself: ON e.manager_id = eh.emp_id matches each employee to a manager row that is already in the result, and eh.level + 1 stamps the child one level deeper than its parent. SQL Server evaluates this member repeatedly. The first pass finds everyone reporting to Alice (Ben and Cara at level 2); the next pass finds their reports (Deng, Ella, Finn at level 3); the pass after that finds Gita at level 4. See recursive CTEs in the advanced interview questions for more worked examples.
Termination is automatic: recursion stops when a pass returns zero new rows. Once every employee has been attached to the tree, the next join finds no unmatched children, the recursive member produces nothing, and the CTE is complete. You do not write an explicit stop condition, but the join predicate must eventually run dry, otherwise the query would loop forever.
The OPTION (MAXRECURSION 100) hint is a safety valve. By default SQL Server caps recursion at 100 levels and raises an error if the CTE goes deeper, which usually signals a cycle in the data (for example a manager who reports to their own subordinate). You can raise the cap (up to MAXRECURSION 32767) for genuinely deep trees, or set MAXRECURSION 0 to remove the limit entirely - but only once you are sure the data really is a tree with no loops. A common mistake is forgetting the anchor filter and selecting every row in the anchor, which double counts employees and breaks the level numbering.