← All tasksSQL · Senior

All subordinates with a recursive CTE

List all direct and indirect subordinates of the employee with id = 1: columns id, name and depth (1 for a direct report, 2 for a report's report and so on). Order by depth, then id. The depth of the hierarchy isn't known in advance.

Tables

CREATE TABLE employees (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  manager_id int REFERENCES employees (id)  -- NULL for the CEO
);

SELECT id, name, 1 AS depth
FROM employees
WHERE manager_id = 1
ORDER BY id;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO employees VALUES
  (1, 'CEO', NULL),
  (2, 'CTO', 1), (3, 'CFO', 1),
  (4, 'Dev lead', 2), (5, 'Accountant', 3),
  (6, 'Developer', 4), (7, 'Intern', 6);

-- another branch of the company is not included
INSERT INTO employees VALUES
  (1, 'Head of A', NULL), (9, 'Head of B', NULL),
  (2, 'A1', 1), (3, 'B1', 9), (4, 'A2', 2);

-- nobody reports to employee 1
INSERT INTO employees VALUES (1, 'Solo', NULL), (2, 'Other', NULL);