Discovering POSGRESQL - CTE

recursive.sql

WITH RECURSIVE
  subdept AS (
    SELECT
      d.department_id,
      d.department_parent_id,
      d.name department_name
    FROM department d
      JOIN employee e USING (department_id)
    WHERE e.employee_id = 13
    UNION ALL
    SELECT
      ds.department_id,
      ds.department_parent_id,
      ds.name AS department_name
    FROM department ds
      JOIN subdept d ON ds.department_id = d.department_parent_id
  ),

  department_people AS (
    SELECT
      coworker.employee_id,
      lead(coworker.name) OVER employee_department_wdw,
      lag(coworker.name) OVER employee_department_wdw
    FROM
      employee emp
      JOIN employee coworker USING (department_id)
    WHERE
      emp.employee_id = 13
    WINDOW
      employee_department_wdw AS (
      ORDER BY coworker.birthdate DESC )
  )

SELECT
  emp.name,
  people.lead,
  people.lag,
  array_agg(subdept.department_name) breadcrumbs
FROM
  employee emp
  JOIN department_people people USING (employee_id),
  subdept
WHERE
  emp.employee_id = 13
GROUP BY emp.name, people.lead, people.lag;