Org chart drills: self-joins and recursion

An org chart lives in one table that points at itself, and interviewers like it because two questions on the same nine rows need two different tools. Comparing an employee with their own manager is a self-join: the table twice, under two aliases. Reaching the whole team below somebody is recursion: a query that reads its own output until no new rows arrive. The data below has the two traps that separate a careful answer from a quick one, an employee with no manager and an employee whose manager left, and later a cycle that an import created.

Two problems on one org chart

The table employees(id, name, manager_id, hired_on, salary) holds nine employees. manager_id is the id of another employee, NULL for the person at the top and, after a bad import, sometimes an id that no employee has.

Problem A: each employee beside their manager. Return every one of the nine employees with their manager’s name, and mark the two cases where there is none: NULL, and an id pointing at nobody. Then answer two questions on the same shape: who was hired before the person who now manages them, and who earns more than their manager.

Problem B: every manager with their whole team. For each employee who manages anybody, return how many employees are below them at any depth and what those employees cost in salary. The chart is four levels deep today and may grow. Sizes: up to 2 × 10⁵ employees, up to 12 levels.

The brute force, and why it fails

Both problems have an answer that uses joins only. The first is a plain JOIN to the manager; the second joins the table to itself once per level.

-- Nine employees. Asha has no manager, Jaya's manager left and her manager_id points nowhere,
-- and two employees were hired before the person who now manages them.
CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INTEGER,
  hired_on   TEXT NOT NULL,
  salary     INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Asha',   NULL, '2019-04-01', 240000),
  (2, 'Bilal',  1,    '2020-07-15', 180000),
  (3, 'Chitra', 1,    '2018-02-20', 175000),
  (4, 'Dev',    2,    '2021-09-01', 120000),
  (5, 'Esha',   2,    '2019-11-11', 130000),
  (6, 'Farhan', 3,    '2022-03-07', 110000),
  (7, 'Gauri',  5,    '2023-06-19',  90000),
  (8, 'Harsh',  5,    '2024-01-08',  85000),
  (9, 'Jaya',   99,   '2022-12-01',  95000);

-- Problem A: every employee with their manager, and who was hired first.
-- A plain JOIN loses Asha, who has no manager, and Jaya, whose manager is missing.
SELECT e.name AS employee, m.name AS manager,
       CASE WHEN e.hired_on < m.hired_on THEN 'before the manager' ELSE 'after the manager' END AS hired
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;

-- How many rows each form returns: 9 employees, 7 with a manager who exists.
SELECT (SELECT count(*) FROM employees) AS employees,
       (SELECT count(*) FROM employees AS e JOIN employees AS m ON m.id = e.manager_id) AS with_inner_join,
       (SELECT count(*) FROM employees AS e LEFT JOIN employees AS m ON m.id = e.manager_id) AS with_left_join;

-- Problem B, brute force: the whole team under each manager, found by joining level by level.
-- Three joins reach three levels down, and a fourth level would need a fourth join.
SELECT a.name AS manager,
       count(DISTINCT b.id) + count(DISTINCT c.id) + count(DISTINCT d.id) AS reports_found
FROM employees AS a
LEFT JOIN employees AS b ON b.manager_id = a.id
LEFT JOIN employees AS c ON c.manager_id = b.id
LEFT JOIN employees AS d ON d.manager_id = c.id
GROUP BY a.id, a.name
HAVING reports_found > 0
ORDER BY a.id;

┌──────────┬─────────┬────────────────────┐
│ employee │ manager │       hired        │
├──────────┼─────────┼────────────────────┤
│ Bilal    │ Asha    │ after the manager  │
│ Chitra   │ Asha    │ before the manager │
│ Dev      │ Bilal   │ after the manager  │
│ Esha     │ Bilal   │ before the manager │
│ Farhan   │ Chitra  │ after the manager  │
│ Gauri    │ Esha    │ after the manager  │
│ Harsh    │ Esha    │ after the manager  │
└──────────┴─────────┴────────────────────┘
┌───────────┬─────────────────┬────────────────┐
│ employees │ with_inner_join │ with_left_join │
├───────────┼─────────────────┼────────────────┤
│ 9         │ 7               │ 9              │
└───────────┴─────────────────┴────────────────┘
┌─────────┬───────────────┐
│ manager │ reports_found │
├─────────┼───────────────┤
│ Asha    │ 7             │
│ Bilal   │ 4             │
│ Chitra  │ 1             │
│ Esha    │ 2             │
└─────────┴───────────────┘

The first result has seven rows, not nine: the plain JOIN silently drops Asha, who has no manager, and Jaya, whose manager_id is 99. The counts confirm it, 7 against 9. The level-by-level answer to problem B gets the right numbers here, but only because three joins happen to reach the bottom of a four-level chart: one more level and it quietly undercounts, and the query has to be rewritten whenever the company reorganises. It also double-counts nothing only because count(DISTINCT …) is used per level.

What to notice

• A self-join needs aliases, because both sides are the same table, and the alias is what makes e.manager_id = m.id readable.
• Keep the rows you are asked about. The question says “every employee”, so the join to the manager is a LEFT JOIN, and a NULL manager and a missing one are different answers.
• One join reaches one level. A question with “at any depth”, “the whole team” or “the chain to the top” needs recursion, not more joins.

Problem A: the self-join, with every employee kept Pro: opens with a Pro, Premium or Ultimate pass