Walk a Hierarchy: WITH RECURSIVE

A classic interview question: each employee row stores the id of its manager. List everyone who works under Tom Clark, directly or through other managers, and how many levels below him they are. A self-join reaches one level down, and you don't know how deep the tree goes. A recursive CTE keeps joining, one level per round, until a round finds nobody.

In this lesson, shop.db has an employees table. Rosa Diaz is the boss, so her manager_id is NULL:

id | name          | manager_id
1  | Rosa Diaz     | NULL
2  | Tom Clark     | 1
3  | Ivy Chen      | 1
4  | Sam Patel     | 2
5  | Lena Fischer  | 2
6  | Omar Haddad   | 4
7  | Nina Kowalski | 4
8  | Paul Martin   | 3
9  | Zoe Becker    | 6

How it works: WITH RECURSIVE builds a CTE from two queries joined by UNION ALL. The anchor runs once and gives the first rows. The step reads the CTE itself and runs again on the rows the round before added, until it adds none. The column names come from the anchor. This one counts from 1 to 5:

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers

Here the anchor is Tom's direct reports at level 1, and the step adds the people whose manager is already in the team, one level deeper. Your query must print:

name | level
Lena Fischer | 1
Sam Patel | 1
Nina Kowalski | 2
Omar Haddad | 2
Zoe Becker | 3
(5 rows)

Your task: the starting query already ends with SELECT name, level FROM team ORDER BY level, name. Above it, write the recursive CTE team: an anchor that selects id, name and 1 AS level from employees where manager_id = 2 (Tom Clark), then UNION ALL, then a step that selects each employee's id, name and team.level + 1, joining team on employees.manager_id = team.id.

Assignment
Follow the Coding Tutorial to wrap the final SELECT in WITH RECURSIVE team AS ( … ) and type the anchor, UNION ALL and the step, one line at a time.

Hint
The anchor reads only employees; only the step reads team. A new person belongs in the team when their manager_id matches the id of someone already in it.