Advanced Recursion — Bill of Materials, Cost Rollup & Shortest Path

We covered basic org-tree recursion earlier. This lesson pushes into the genuinely hard recursive patterns: bill of materials with aggregated cost rollup (each parent's cost = sum of all descendants) and shortest path in a weighted graph — both classic interview problems that require recursive CTEs to solve in pure SQL.

Bill of Materials with Cost Rollup

-- BOM: products made from sub-components made from raw materials
CREATE TABLE IF NOT EXISTS bom_components (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    parent_id   INT REFERENCES bom_components(id),
    qty_needed  NUMERIC(10,4) DEFAULT 1,  -- quantity of this component per parent unit
    unit_cost   NUMERIC(10,4) DEFAULT 0   -- leaf cost (0 for assemblies)
);

INSERT INTO bom_components (id, name, parent_id, qty_needed, unit_cost)
VALUES
    (1,  'Bicycle',          NULL, 1,    0),
    (2,  'Frame Assembly',   1,    1,    0),
    (3,  'Wheel Assembly',   1,    2,    0),   -- 2 wheels per bike
    (4,  'Drivetrain',       1,    1,    0),
    (5,  'Steel Tubing',     2,    3,    12.50),
    (6,  'Welds',            2,    8,    0.75),
    (7,  'Paint',            2,    1,    8.00),
    (8,  'Rim',              3,    1,    22.00),
    (9,  'Spokes',           3,    32,   0.30),
    (10, 'Tyre',             3,    1,    18.00),
    (11, 'Hub',              3,    1,    14.50),
    (12, 'Chain',            4,    1,    9.00),
    (13, 'Cassette',         4,    1,    28.00),
    (14, 'Derailleur',       4,    1,    45.00),
    (15, 'Crankset',         4,    1,    38.00)
ON CONFLICT DO NOTHING;

-- Recursive BOM explosion with cumulative quantity and cost rollup
WITH RECURSIVE bom AS (
    -- Anchor: top-level product
    SELECT
        id,
        name,
        parent_id,
        qty_needed,
        unit_cost,
        qty_needed::NUMERIC             AS cumulative_qty,  -- qty relative to root
        0                               AS depth,
        ARRAY[id]                       AS path,
        name                            AS full_path
    FROM bom_components
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.name,
        c.parent_id,
        c.qty_needed,
        c.unit_cost,
        b.cumulative_qty * c.qty_needed AS cumulative_qty,  -- multiply down the tree
        b.depth + 1,
        b.path || c.id,
        b.full_path || ' > ' || c.name
    FROM bom_components c
    JOIN bom b ON b.id = c.parent_id
)
SELECT
    REPEAT('  ', depth) || name       AS component,
    ROUND(cumulative_qty, 4)            AS qty_per_bicycle,
    ROUND(unit_cost, 2)                 AS unit_cost_usd,
    ROUND(cumulative_qty * unit_cost, 4) AS extended_cost_usd,
    depth,
    full_path
FROM bom
ORDER BY path;