Common Table Expression (CTE)

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO Employee (id, name, manager_id)
VALUES
    (1, 'Alice', NULL),
    (2, 'Bob', 1),
    (3, 'Charlie', 1),
    (4, 'David', 2),
    (5, 'Eva', 2),
    (6, 'Frank', 3),
    (7, 'Grace', 3);

WITH RECURSIVE employee_cte AS (
    SELECT
        id,
        name,
        manager_id,
        0 AS level
    FROM
        Employee
    WHERE
        manager_id IS NULL
    UNION ALL
    SELECT
        e.id,
        e.name,
        e.manager_id,
        cte.level + 1
    FROM
        Employee e
    INNER JOIN
        employee_cte cte ON e.manager_id = cte.id
)
SELECT
    id,
    name,
    manager_id,
    level
FROM
    employee_cte;