Raw materials needed to build one finished product

A manufacturing planner stores a bill of materials in bom: each row says that one unit of parent_item needs qty_per_parent units of child_item. Sub-assemblies are themselves built from other items, to any depth, and the same raw part can appear in several branches.

For the finished product Smart Speaker, return every raw component (an item that is never a parent of anything) with the total_qty needed to build one speaker. Quantities multiply down the tree and must be added up across branches. Order by component.

1. Step 1: The anchor member starts with the direct children of Smart Speaker and their quantities.
2. Step 2: The recursive member joins each found item to its own children and multiplies the quantities, so Capacitor via Speaker Driver is 2 drivers x 2 = 4.
3. Step 3: `UNION ALL` is required so that identical quantities arriving by different paths are not collapsed.
4. Step 4: Raw components are items that are never a `parent_item` (`NOT EXISTS`). Finally `SUM` merges the same part reached through several branches: Capacitor = 12 (Mainboard) + 4 (Speaker Driver) = 16. Bluetooth Headset rows are never reached.

WITH RECURSIVE explode AS (
    SELECT child_item     AS component,
           qty_per_parent AS total_qty
    FROM bom
    WHERE parent_item = 'Smart Speaker'

    UNION ALL

    SELECT b.child_item,
           e.total_qty * b.qty_per_parent
    FROM explode e
    JOIN bom b ON b.parent_item = e.component
)
SELECT e.component,
       SUM(e.total_qty) AS total_qty
FROM explode e
WHERE NOT EXISTS (SELECT 1 FROM bom b WHERE b.parent_item = e.component)
GROUP BY e.component
ORDER BY e.component;