Hierarchies with Recursive CTEs

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    manager_id INT NULL,
    hired_on DATE NOT NULL
);

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

Asha Rao (1)
├── Ben Carter (2)
│   ├── Chitra Nair (3)
│   └── Dev Malhotra (4)
├── Esha Gupta (5)
│   ├── Farid Khan (6)
│   └── Gita Menon (7)
└── Hari Iyer (8)

WITH RECURSIVE reports AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE name = 'Esha Gupta'
    UNION ALL
    SELECT e.id, e.name, e.manager_id, r.level + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.id
)
SELECT level, name FROM reports ORDER BY level, name;

+-------+------------+
| level | name       |
+-------+------------+
|     0 | Esha Gupta |
|     1 | Farid Khan |
|     1 | Gita Menon |
+-------+------------+
3 rows in set

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS depth, CAST(LPAD(id, 4, '0') AS CHAR(200)) AS sort_path, CAST(name AS CHAR(300)) AS name_path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1, CONCAT(o.sort_path, '/', LPAD(e.id, 4, '0')), CONCAT(o.name_path, ' > ', e.name)
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT CONCAT(REPEAT('    ', depth - 1), name) AS org_chart, depth, name_path
FROM org
ORDER BY sort_path;

+----------------------+-------+--------------------------------------+
| org_chart            | depth | name_path                            |
+----------------------+-------+--------------------------------------+
| Asha Rao             |     1 | Asha Rao                             |
|     Ben Carter       |     2 | Asha Rao > Ben Carter                |
|         Chitra Nair  |     3 | Asha Rao > Ben Carter > Chitra Nair  |
|         Dev Malhotra |     3 | Asha Rao > Ben Carter > Dev Malhotra |
|     Esha Gupta       |     2 | Asha Rao > Esha Gupta                |
|         Farid Khan   |     3 | Asha Rao > Esha Gupta > Farid Khan   |
|         Gita Menon   |     3 | Asha Rao > Esha Gupta > Gita Menon   |
|     Hari Iyer        |     2 | Asha Rao > Hari Iyer                 |
+----------------------+-------+--------------------------------------+
8 rows in set

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 0 AS steps FROM employees WHERE name = 'Farid Khan'
    UNION ALL
    SELECT m.id, m.name, m.manager_id, c.steps + 1 FROM employees m JOIN chain c ON m.id = c.manager_id
)
SELECT steps, name FROM chain ORDER BY steps;

+-------+------------+
| steps | name       |
+-------+------------+
|     0 | Farid Khan |
|     1 | Esha Gupta |
|     2 | Asha Rao   |
+-------+------------+
3 rows in set

WITH RECURSIVE tree AS (
    SELECT id AS root_id, id AS member_id FROM employees
    UNION ALL
    SELECT t.root_id, e.id FROM tree t JOIN employees e ON e.manager_id = t.member_id
)
SELECT r.name AS manager, COUNT(*) - 1 AS people_below, SUM(m.salary) - r.salary AS salary_below
FROM tree t
JOIN employees r ON r.id = t.root_id
JOIN employees m ON m.id = t.member_id
GROUP BY r.id, r.name, r.salary
HAVING COUNT(*) > 1
ORDER BY people_below DESC, r.name;

+------------+--------------+--------------+
| manager    | people_below | salary_below |
+------------+--------------+--------------+
| Asha Rao   |            7 |    553000.00 |
| Ben Carter |            2 |    183000.00 |
| Esha Gupta |            2 |    128000.00 |
+------------+--------------+--------------+
3 rows in set

CREATE TABLE links (id INT PRIMARY KEY, parent_id INT);
INSERT INTO links VALUES (1, 3), (2, 1), (3, 2);

WITH RECURSIVE walk AS (
    SELECT id, parent_id, CAST(id AS CHAR(100)) AS visited, 0 AS is_cycle FROM links WHERE id = 1
    UNION ALL
    SELECT l.id, l.parent_id, CONCAT(w.visited, ',', l.id), FIND_IN_SET(l.id, w.visited) > 0
    FROM links l JOIN walk w ON l.id = w.parent_id
    WHERE w.is_cycle = 0
)
SELECT id, visited, is_cycle FROM walk ORDER BY LENGTH(visited);

+------+---------+----------+
| id   | visited | is_cycle |
+------+---------+----------+
|    1 | 1       |        0 |
|    3 | 1,3     |        0 |
|    2 | 1,3,2   |        0 |
|    1 | 1,3,2,1 |        1 |
+------+---------+----------+
4 rows in set

CREATE TABLE parts (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE bom (parent_id INT, child_id INT, quantity INT);
INSERT INTO parts VALUES (1, 'Bicycle'), (2, 'Wheel'), (3, 'Frame'), (4, 'Spoke'), (5, 'Tyre');
INSERT INTO bom VALUES (1, 2, 2), (1, 3, 1), (2, 4, 32), (2, 5, 1);

WITH RECURSIVE needs AS (
    SELECT child_id AS part_id, quantity AS total_quantity FROM bom WHERE parent_id = 1
    UNION ALL
    SELECT b.child_id, n.total_quantity * b.quantity FROM needs n JOIN bom b ON b.parent_id = n.part_id
)
SELECT p.name, SUM(n.total_quantity) AS needed_for_one_bicycle
FROM needs n JOIN parts p ON p.id = n.part_id
GROUP BY p.id, p.name
ORDER BY p.id;

+-------+------------------------+
| name  | needed_for_one_bicycle |
+-------+------------------------+
| Wheel |                      2 |
| Frame |                      1 |
| Spoke |                     64 |
| Tyre  |                      2 |
+-------+------------------------+
4 rows in set