How do you write a SQL query to get employees without a manager and display the hierarchy?

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100),
    role       VARCHAR(100),
    manager_id INT REFERENCES employees(id)  -- self-referencing
);

INSERT INTO employees VALUES
(1, 'CEO Sarah',    'CEO',      NULL),   -- top level, no manager
(2, 'CTO Mike',     'CTO',      1),
(3, 'QA Lead Amy',  'QA Lead',  2),
(4, 'SDET Alice',   'SDET',     3),
(5, 'SDET Bob',     'SDET',     3),
(6, 'Dev Lead Dan', 'Dev Lead', 2),
(7, 'Dev Eve',      'Developer',6);

SELECT id, name, role
FROM employees
WHERE manager_id IS NULL;

-- Result:
-- 1 | CEO Sarah | CEO

-- Who reports directly to the CTO (id = 2)?
SELECT e.id, e.name, e.role
FROM employees e
WHERE e.manager_id = 2;

-- Result:
-- 3 | QA Lead Amy  | QA Lead
-- 6 | Dev Lead Dan | Dev Lead

WITH RECURSIVE hierarchy AS (
    -- Anchor: start from the top (no manager)
    SELECT
        id,
        name,
        role,
        manager_id,
        0 AS depth,
        CAST(name AS VARCHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: join each employee to their manager
    SELECT
        e.id,
        e.name,
        e.role,
        e.manager_id,
        h.depth + 1,
        CONCAT(h.path, ' → ', e.name)  -- build path
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT
    REPEAT('  ', depth) || name AS indented_name,
    role,
    depth AS level,
    path
FROM hierarchy
ORDER BY path;

-- Result:
-- CEO Sarah              | CEO      | 0 | CEO Sarah
--   CTO Mike             | CTO      | 1 | CEO Sarah → CTO Mike
--     Dev Lead Dan       | Dev Lead | 2 | CEO Sarah → CTO Mike → Dev Lead Dan
--       Dev Eve          | Developer| 3 | ...
--     QA Lead Amy        | QA Lead  | 2 | ...
--       SDET Alice       | SDET     | 3 | ...
--       SDET Bob         | SDET     | 3 | ...

-- Find everyone under CTO Mike (id = 2), any number of levels deep
WITH RECURSIVE subordinates AS (
    -- Start with direct reports of CTO
    SELECT id, name, role, manager_id, 1 AS level
    FROM employees
    WHERE manager_id = 2

    UNION ALL

    -- Recursively get their reports
    SELECT e.id, e.name, e.role, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT name, role, level
FROM subordinates
ORDER BY level, name;

-- Result: Amy, Dan (level 1), Alice, Bob, Eve (level 2)

WITH RECURSIVE subordinates AS (
    SELECT id, manager_id FROM employees WHERE manager_id IS NOT NULL
    UNION ALL
    SELECT e.id, s.manager_id
    FROM employees e JOIN subordinates s ON e.manager_id = s.id
)
SELECT m.name AS manager, COUNT(s.id) AS total_reports
FROM employees m
LEFT JOIN subordinates s ON s.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY total_reports DESC;