Recursive PostgreSQL CTE example for seeing chain of command and subordinates for employees.

recursive-employees-with-subordinates.postgres.sql

WITH RECURSIVE subordinates AS ( -- CTE for employees with chain of command
  WITH employees AS ( -- CTE for example employees
    SELECT 1 AS id, 'Davy Crockett' AS full_name, NULL AS manager_id
    UNION SELECT 2 AS id, 'Cindy Cruz' AS full_name, 1 AS manager_id
    UNION SELECT 3 AS id, 'Harry Thompson' AS full_name, 1 AS manager_id
    UNION SELECT 4 AS id, 'George Jefferson' AS full_name, 2 AS manager_id
    UNION SELECT 5 AS id, 'Blinky Bill' AS full_name, 2 AS manager_id
    UNION SELECT 6 AS id, 'Linda Vernsky' AS full_name, 1 AS manager_id
    UNION SELECT 7 AS id, 'Abigail Quispe' AS full_name, 1 AS manager_id
    UNION SELECT 8 AS id, 'Jessica Ericson' AS full_name, 7 AS manager_id
    UNION SELECT 9 AS id, 'Matthew Haywood' AS full_name, 7 AS manager_id
    UNION SELECT 10 AS id, 'Angel Jones' AS full_name, 7 AS manager_id
    UNION SELECT 11 AS id, 'Chris Mackenzie' AS full_name, 7 AS manager_id
    UNION SELECT 12 AS id, 'Gustavo Frederickson' AS full_name, 5 AS manager_id
    UNION SELECT 13 AS id, 'Joan Smith' AS full_name, 5 AS manager_id
    UNION SELECT 14 AS id, 'Debora Ferreira' AS full_name, 5 AS manager_id
  )
  SELECT -- non-recursive term
    id,
    manager_id,
    full_name,
    ARRAY[full_name] AS chain_of_command_names,
    ARRAY[id] AS chain_of_command_ids,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION
  SELECT -- recursive term
    e.id,
    e.manager_id,
    e.full_name,
    array_append(s.chain_of_command_names, e.full_name) AS chain_of_command_names,
    array_append(s.chain_of_command_ids, e.id) AS chain_of_command_ids,
    s.level + 1 AS level
  FROM employees AS e
  JOIN subordinates AS s
    ON s.id = e.manager_id
)
SELECT
  a.id,
  a.full_name,
  a.chain_of_command_names,
  array_remove(array_agg(b.full_name), NULL) AS subordinates
FROM subordinates AS a
LEFT JOIN subordinates AS b -- join provides subordinates
  ON a.id = ANY (b.chain_of_command_ids)
  AND a.level < b.level
GROUP BY 1, 2, 3
ORDER BY 1;

The simple version is here.

Referenced by http://cwestblog.com/2018/10/29/postgresql-recursively-query-hierarchical-data/