Bill-of-Materials Hierarchy (Recursive Walk)
Use a recursive CTE to display the complete bill of materials for product_id 1 (Bicycle). Quantities multiply down the tree (a Wheel needs 2, each Wheel has 32 Spokes → 64 Spokes total). Return product_id, product_name, component_id, component_name, quantity, level — ordered by level, component_id.
- Recursive CTE
- CTEs
- Joins
- Subqueries
- Filtering
Exercise brief
Understand the request
Supply chain analyst Manufacturing needs an exploded bill of materials with quantities multiplied through every assembly level.
Return
- Return parent and component identity, cumulative quantity, and level.
- Order by level and component_id.
Constraints
- Anchor on product_id 1.
- Multiply quantities across the recursive step.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bill_of_materials
product_idINTEGERproduct_nameTEXTcomponent_idINTEGERcomponent_nameTEXTquantityINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Anchor: rows where product_id = 1 (top of the tree).
Hint 2
Recursive step: a component becomes a product at the next level (bom.product_id = bh.component_id).
Hint 3
Multiply quantity through the chain — that is what gives you the true total for each leaf.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE bom_hierarchy AS (SELECT product_id, product_name, component_id, component_name, quantity, 1 AS level FROM bill_of_materials WHERE product_id = 1 UNION ALL SELECT bh.product_id, bh.product_name, bom.component_id, bom.component_name, bh.quantity * bom.quantity AS quantity, bh.level + 1 FROM bill_of_materials bom INNER JOIN bom_hierarchy bh ON bom.product_id = bh.component_id) SELECT product_id, product_name, component_id, component_name, quantity, level FROM bom_hierarchy ORDER BY level, component_id;Why this works
BoM explosion is the manufacturing equivalent of org-chart traversal. The recursive multiplication of quantities is the core feature — it turns "1 bike → 2 wheels → 32 spokes per wheel" into "64 spokes per bike".
Success check
Every Bicycle component appears at the correct depth with its fully extended quantity.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| product_id | product_name | component_id | component_name | quantity | level |
|---|---|---|---|---|---|
| 1 | Bicycle | 2 | Frame | 1 | 1 |
| 1 | Bicycle | 3 | Wheel | 2 | 1 |
| 1 | Bicycle | 4 | Steel Tube | 3 | 2 |
| 1 | Bicycle | 5 | Weld Joint | 6 | 2 |
| 1 | Bicycle | 6 | Rim | 2 | 2 |
| 1 | Bicycle | 7 | Spoke | 64 | 2 |
| 1 | Bicycle | 8 | Hub | 2 | 2 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Open the interactive workspace and practice across SQL topics.