Recursive CTE SQL: Hierarchies, Date Series, and Graph...

WITH RECURSIVE cte_name AS (
  -- Anchor member: runs once
  SELECT employee_id, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member: runs until zero rows
  SELECT e.employee_id, e.manager_id, c.depth + 1
  FROM employees e
  INNER JOIN cte_name c ON e.manager_id = c.employee_id
  WHERE c.depth < 20
)
SELECT * FROM cte_name;

-- Org chart: find all employees under manager_id = 1
WITH RECURSIVE reports AS (
  -- Base case: direct reports of manager 1
  SELECT
    employee_id,
    emp_name,
    manager_id,
    1 AS depth
  FROM employees
  WHERE manager_id = 1

  UNION ALL

  -- Recursive case: find reports of reports
  SELECT
    e.employee_id,
    e.emp_name,
    e.manager_id,
    r.depth + 1
  FROM employees e
  INNER JOIN reports r ON e.manager_id = r.employee_id
  WHERE r.depth < 20  -- safety guard
)
SELECT employee_id, emp_name, depth
FROM reports
ORDER BY depth, emp_name;

Iteration 0: The anchor finds all employees where manager_id = 42. Suppose that returns Alice, Bob, and Carol. Iteration 1: The recursive member finds everyone who reports to Alice, Bob, or Carol. This continues until an iteration produces zero rows.

WITH RECURSIVE org_tree AS (
  -- Anchor: top-level managers
  SELECT
    employee_id,
    emp_name,
    manager_id,
    emp_name AS full_path,
    1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: append report name to manager path
  SELECT
    e.employee_id,
    e.emp_name,
    e.manager_id,
    ot.full_path || ' > ' || e.emp_name,
    ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
  WHERE ot.depth < 30
)
SELECT employee_id, emp_name, full_path, depth
FROM org_tree
ORDER BY full_path;

WITH RECURSIVE org AS (
  -- Anchor: direct reports of the top manager
  SELECT
    manager_id,
    employee_id,
    1 AS headcount,
    1 AS level
  FROM employees
  WHERE manager_id = 1

  UNION ALL

  -- Recursive: drill into reports of reports
  SELECT
    e.manager_id,
    e.employee_id,
    o.headcount AS headcount,
    o.level + 1
  FROM employees e
  INNER JOIN org o ON e.manager_id = o.employee_id
  WHERE o.level < 15
)
SELECT manager_id, SUM(headcount) AS total_reports
FROM org
GROUP BY manager_id
ORDER BY total_reports DESC;

-- Generate every date in Q1 2024
WITH RECURSIVE dates AS (
  SELECT date('2024-01-01') AS dt

  UNION ALL

  SELECT date(dt, '+1 day')
  FROM dates
  WHERE dt < date('2024-03-31')
)
SELECT dt
FROM dates;

WITH RECURSIVE dates AS (
  SELECT date('2024-01-01') AS dt
  UNION ALL
  SELECT date(dt, '+1 day')
  FROM dates
  WHERE dt < date('2024-03-31')
),
daily_events AS (
  SELECT
    date(event_timestamp) AS event_date,
    COUNT(*) AS event_count
  FROM event_data
  WHERE event_timestamp >= '2024-01-01'
    AND event_timestamp < '2024-04-01'
  GROUP BY date(event_timestamp)
)
SELECT
  d.dt AS day,
  COALESCE(de.event_count, 0) AS events
FROM dates d
LEFT JOIN daily_events de ON d.dt = de.event_date
ORDER BY d.dt;

Interview note: When an interviewer asks for daily metrics with no gaps, the date series CTE is the standard answer. Mention it before they ask about missing days. It shows you have dealt with sparse time-series data in production.

-- Find all employees reachable from manager 1 (graph walk)
WITH RECURSIVE reachable AS (
  -- Anchor: start at node 1
  SELECT
    manager_id AS source_node,
    employee_id AS target_node,
    '/' || manager_id || '/' || employee_id || '/' AS visited,
    1 AS hops
  FROM employees
  WHERE manager_id = 1

  UNION ALL

  -- Recursive: follow edges, skip already-visited nodes
  SELECT
    e.manager_id,
    e.employee_id,
    r.visited || e.employee_id || '/',
    r.hops + 1
  FROM employees e
  INNER JOIN reachable r ON e.manager_id = r.target_node
  WHERE instr(r.visited, '/' || e.employee_id || '/') = 0
    AND r.hops < 10
)
SELECT target_node, MIN(hops) AS shortest_hops
FROM reachable
GROUP BY target_node
ORDER BY shortest_hops;

-- Find all upstream managers of employee 11
WITH RECURSIVE upstream AS (
  SELECT
    manager_id AS employee_id,
    1 AS distance
  FROM employees
  WHERE employee_id = 11

  UNION ALL

  SELECT
    e.manager_id,
    u.distance + 1
  FROM employees e
  INNER JOIN upstream u ON e.employee_id = u.employee_id
  WHERE e.manager_id IS NOT NULL
    AND u.distance < 20
)
SELECT employee_id, MIN(distance) AS min_distance
FROM upstream
WHERE employee_id IS NOT NULL
GROUP BY employee_id
ORDER BY min_distance;

-- Depth limit (works everywhere)
WHERE ct.depth < 50

-- PostgreSQL: array-based cycle detection
WHERE NOT e.target_id = ANY(r.visited_ids)
  AND r.depth < 50

Interview note: Mentioning termination conditions unprompted is a strong signal. After writing your recursive CTE, say: In production I would add a depth guard and possibly array-based cycle detection to handle dirty data.