Manufacturing and e-commerce systems often use a bill of materials (BOM), such as "Product A needs 2 units of component B, and component B needs 3 units of material C." To calculate the required quantity of every component for one finished product, recursively expand the structure while multiplying the parent quantity by the BOM quantity.

WITH RECURSIVE bom_tree AS (
  -- ① Anchor: start the finished product with qty=1
  SELECT part_id, part_name, unit_cost, 1 AS qty, 0 AS depth
  FROM  parts WHERE part_id = :root_id

  UNION ALL

  -- ② Recursive member: retrieve child parts from BOM and accumulate quantity by multiplication
  SELECT p.part_id, p.part_name, p.unit_cost,
         bt.qty * b.qty,          -- ← parent qty × BOM qty
         bt.depth + 1
  FROM  bom b
  JOIN  bom_tree bt ON b.parent_id = bt.part_id
  JOIN  parts p    ON b.child_id  = p.part_id
)

There are product-structure tables (parts and bom). Expand every direct and indirect component of finished product A (part_id=1) and return depth, path, part_name, qty (cumulative quantity), unit_cost, line_cost (qty × unit_cost). Exclude finished product A itself and sort by depth, part_id ascending.

Expected output (depth, part_id ascending):

SUM(line_cost) = 500+2000+1200+1600+600 = 5900 is the total component cost per finished product A.

WITH RECURSIVE bom_tree AS (

  -- ① Anchor member: start Finished Product A (part_id=1) with qty=1, depth=0
  SELECT
    p.part_id,
    p.part_name,
    p.unit_cost,
    1                     AS qty,    -- start with one finished product
    0                     AS depth,
    p.part_name           AS path
  FROM  parts p
  WHERE p.part_id = 1

  UNION ALL

  -- ② Recursive member: retrieve child parts from BOM and accumulate quantity by multiplication
  SELECT
    p.part_id,
    p.part_name,
    p.unit_cost,
    bt.qty * b.qty        AS qty,   -- cumulative quantity = parent qty × BOM qty
    bt.depth + 1,
    bt.path || ' > ' || p.part_name
  FROM  bom b
  JOIN  bom_tree bt ON b.parent_id = bt.part_id  -- join parent part_id to BOM parent_id
  JOIN  parts p    ON b.child_id  = p.part_id   -- retrieve child-part details from parts

)
SELECT
  depth,
  path,
  part_name,
  qty,
  unit_cost,
  qty * unit_cost         AS line_cost  -- cumulative quantity × unit cost = line cost
FROM  bom_tree
WHERE part_id <> 1                      -- exclude the finished product itself (unit_cost=0)
ORDER BY depth, part_id;

/*
  Execution order (SQL logical evaluation order):
  1. Anchor member
  2. Recursion iteration 1
  3. Recursion iteration 2
  4. Recursion iteration 3
  5. Outer query
  */

Reports and analysis often need to show an entire period, including days with no sales or time slots with no logs. Because the database contains rows only for days with sales, a standard pattern is to generate a date sequence with WITH RECURSIVE and fill gaps with LEFT JOIN.

WITH RECURSIVE cal AS (
  -- ① Anchor member: specify the start date (no table reference)
  SELECT DATE '2024-01-01' AS d

  UNION ALL

  -- ② Recursive member: add one day at a time through the end date
  SELECT (d + INTERVAL '1 day')::date   -- PostgreSQL
  -- SELECT DATE_ADD(d, INTERVAL 1 DAY)    -- MySQL
  FROM  cal
  WHERE d < DATE '2024-01-07'
)

The sales_daily table contains rows only for days with sales. For all seven days from 2024-01-01 through 2024-01-07, return the amount when sales exist and 0 otherwise. Return sale_date, amount, status ('Present' or 'Missing') and sort by sale_date ascending.

Expected output (sale_date ascending):

All seven days are returned. Missing days are filled with amount=0 and status='Missing'.

WITH RECURSIVE date_series AS (

  -- ① Anchor member: set the start date 2024-01-01 (no table reference)
  SELECT DATE '2024-01-01' AS d

  UNION ALL

  -- ② Recursive member: add one day at a time (cast to PostgreSQL date)
  -- MySQL: SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d < '2024-01-07'
  SELECT (d + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  d < DATE '2024-01-07'    -- stop when the end date is reached

)
SELECT
  ds.d                                     AS sale_date,
  COALESCE(s.amount, 0)                   AS amount,  -- fill NULL with 0
  CASE WHEN s.sale_date IS NULL
       THEN 'Missing' ELSE 'Present'
  END                                      AS status
FROM  date_series ds
LEFT JOIN sales_daily s ON ds.d = s.sale_date  -- no-sales days join as NULL
ORDER BY ds.d;

/*
  Execution order (SQL logical evaluation order):
  1. Anchor member
  2. Recursion iterations 1–5
  3. Recursion iteration 6
  4. Recursion iteration 7 attempt
  5. Outer query
  */

Directed graphs appear in organization workflows, transportation networks, and social-media follow relationships. Common questions are whether node A can reach node B and how many hops the shortest route takes. WITH RECURSIVE can find shortest hops in a form similar to breadth-first search (BFS).

WITH RECURSIVE paths AS (
  -- ① Anchor member: set directly reachable edges from the start at hops=1
  SELECT from_node, to_node, 1 AS hops,
         from_node || '->' || to_node AS path
  FROM  edges WHERE from_node = :start

  UNION ALL

  -- ② Recursive member: extend the path by one hop
  SELECT p.from_node, e.to_node, p.hops + 1,
         p.path || '->' || e.to_node
  FROM  edges e
  JOIN  paths p ON e.from_node = p.to_node
  WHERE p.hops + 1 <= 5   -- guard against infinite loops
)

There is a directed graph in the edges table. Find every node reachable from node 'A', its shortest hop count, and its path string. Return to_node, hops, path (excluding A itself), keep only the shortest path for each destination, and sort by hops, to_node ascending.

Expected output (shortest hops only, hops, to_node ascending):

D and E have multiple paths, but only the shortest-hop path is returned. If hop counts tie, choose the lexicographically earliest path.

WITH RECURSIVE paths AS (

  -- ① Anchor member: retrieve edges leaving node 'A' directly (hops=1)
  SELECT
    from_node,
    to_node,
    1                            AS hops,
    from_node || '->' || to_node AS path
  FROM  edges
  WHERE from_node = 'A'

  UNION ALL

  -- ② Recursive member: add an edge leaving the current to_node and extend by one hop
  SELECT
    p.from_node,
    e.to_node,
    p.hops + 1,
    p.path || '->' || e.to_node
  FROM  edges e
  JOIN  paths p ON e.from_node = p.to_node   -- move from the current endpoint
  WHERE p.hops + 1 <= 5                    -- depth guard against loops
    AND  p.path NOT LIKE '%' || e.to_node || '%'  -- prevent revisiting a node already in the path

)
, ranked AS (
  SELECT
    to_node,
    hops,
    path,
    ROW_NUMBER() OVER (
      PARTITION BY to_node
      ORDER BY hops, path
    ) AS rn  -- rank shortest hop first, then lexicographically smallest
  FROM  paths
)
SELECT
  to_node,
  hops,
  path
FROM  ranked
WHERE rn = 1
ORDER BY hops, to_node;

/*
  Execution order (SQL logical evaluation order):
  1. Anchor member
  2. Recursion iteration 1
  3. Recursion iteration 2
  4. ranked CTE (ROW_NUMBER)
  5. Outer query
  */

A recursive CTE enters an infinite loop when hierarchical data contains a cycle in which a node becomes its own ancestor. In practice, there are two approaches: ① the CYCLE clause in PostgreSQL 14+ and ② flag management with a visited-node array or path string. This question covers both.

-- CYCLE clause in PG14+ (most concise)
WITH RECURSIVE tree AS (
  SELECT id, parent_id, id AS root_id FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.root_id FROM nodes n JOIN tree t ON n.parent_id = t.id
) CYCLE id SET is_cycle USING path  -- ← PG14+: detected rows have is_cycle TRUE
SELECT * FROM tree WHERE is_cycle;

-- Portable path-string check (works across DBMSs)
WHERE visited_ids NOT LIKE '%,' || child.id || ',%'  -- expand only unvisited nodes

The nodes table contains a data inconsistency with a cycle. While expanding every node with WITH RECURSIVE, manage visited nodes in a path string and detect the cycle. Return node_id, name, parent_id, depth, path, is_cycle (is_cycle is TRUE when a cycle is detected and FALSE otherwise). Sort by node_id, depth ascending.

Expected output (cycle included, node_id / depth ascending):

An is_cycle=TRUE row represents a revisit to a node already present in the path. Expansion is blocked after depth=4 by the guard.

WITH RECURSIVE tree AS (

  -- ① Anchor member: start from root nodes (parent_id IS NULL)
  SELECT
    node_id,
    name,
    parent_id,
    0                                   AS depth,
    ',' || node_id || ','              AS path,      -- record IDs with commas
    FALSE                               AS is_cycle  -- the anchor is not cyclic
  FROM  nodes
  WHERE parent_id IS NULL

  UNION ALL

  -- ② Recursive member: fetch children; set is_cycle=TRUE if path already contains the child
  SELECT
    n.node_id,
    n.name,
    n.parent_id,
    t.depth + 1,
    t.path || n.node_id || ','         AS path,
    t.path LIKE '%,' || n.node_id || ',%' AS is_cycle  -- detect ',node_id,' already in path
  FROM  nodes n
  JOIN  tree t ON n.parent_id = t.node_id
  WHERE t.depth + 1 <= 10                              -- depth guard (max 10 levels even with a cycle)
    AND  NOT t.is_cycle                                -- do not expand a row where a cycle was already detected

)
SELECT
  node_id,
  name,
  parent_id,
  depth,
  path,
  is_cycle
FROM  tree
ORDER BY node_id, depth;

/*
  Execution order (SQL logical evaluation order):
  1. Anchor member
  2. Recursion iteration 1
  3. Recursion iteration 2
  4. Recursion iteration 3
  5. Cycle detection
  6. Outer query
  */