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

WITH RECURSIVE cte_name AS (
  -- Anchor member: runs once
  SELECT columns
  FROM base_table
  WHERE starting_condition

  UNION ALL

  -- Recursive member: runs until zero rows
  SELECT columns
  FROM base_table
  INNER JOIN cte_name ON join_condition
  WHERE termination_guard
)
SELECT * FROM cte_name;

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

  UNION ALL

  -- Recursive case: find reports of reports
  SELECT
    e.id,
    e.name,
    e.manager_id,
    r.depth + 1
  FROM employees e
  INNER JOIN reports r ON e.manager_id = r.id
  WHERE r.depth < 20  -- safety guard
)
SELECT id, name, depth
FROM reports
ORDER BY depth, 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 category_tree AS (
  -- Anchor: top-level categories
  SELECT
    id,
    name,
    parent_id,
    name::TEXT AS full_path,
    1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive: append child name to parent path
  SELECT
    c.id,
    c.name,
    c.parent_id,
    ct.full_path || ' > ' || c.name,
    ct.depth + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.depth < 30
)
SELECT id, name, full_path, depth
FROM category_tree
ORDER BY full_path;

WITH RECURSIVE bom AS (
  -- Anchor: top-level product
  SELECT
    part_id,
    component_id,
    quantity,
    1 AS level
  FROM parts_hierarchy
  WHERE part_id = 'PRODUCT-001'

  UNION ALL

  -- Recursive: drill into sub-components
  SELECT
    ph.part_id,
    ph.component_id,
    ph.quantity * b.quantity AS quantity,
    b.level + 1
  FROM parts_hierarchy ph
  INNER JOIN bom b ON ph.part_id = b.component_id
  WHERE b.level < 15
)
SELECT component_id, SUM(quantity) AS total_needed
FROM bom
GROUP BY component_id
ORDER BY total_needed DESC;

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

  UNION ALL

  SELECT dt + INTERVAL '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 dt + INTERVAL '1 day'
  FROM dates
  WHERE dt < DATE '2024-03-31'
),
daily_events AS (
  SELECT
    DATE(event_time) AS event_date,
    COUNT(*) AS event_count
  FROM events
  WHERE event_time >= '2024-01-01'
    AND event_time < '2024-04-01'
  GROUP BY DATE(event_time)
)
SELECT
  d.dt AS date,
  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 nodes reachable from node 'A'
WITH RECURSIVE reachable AS (
  -- Anchor: start at node A
  SELECT
    source_node,
    target_node,
    ARRAY[source_node] AS visited,
    1 AS hops
  FROM edges
  WHERE source_node = 'A'

  UNION ALL

  -- Recursive: follow edges, skip visited nodes
  SELECT
    e.source_node,
    e.target_node,
    r.visited || e.target_node,
    r.hops + 1
  FROM edges e
  INNER JOIN reachable r ON e.source_node = r.target_node
  WHERE NOT e.target_node = ANY(r.visited)
    AND r.hops < 10
)
SELECT DISTINCT target_node, MIN(hops) AS shortest_hops
FROM reachable
GROUP BY target_node
ORDER BY shortest_hops;

-- Find all upstream dependencies of task 'final_report'
WITH RECURSIVE upstream AS (
  SELECT
    dependency_task AS task_name,
    1 AS distance
  FROM task_dependencies
  WHERE task_name = 'final_report'

  UNION ALL

  SELECT
    td.dependency_task,
    u.distance + 1
  FROM task_dependencies td
  INNER JOIN upstream u ON td.task_name = u.task_name
  WHERE u.distance < 20
)
SELECT DISTINCT task_name, MIN(distance) AS min_distance
FROM upstream
GROUP BY task_name
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.