Bill of Materials - Recursive Cost Explosion
Preview mode. Log in to edit, run, submit, and save progress.
Bill of Materials - Recursive Cost Explosion
You are given a product Components table and a BOM (Bill of Materials) table. Each product can have sub-components, each of which may itself have sub-components (multi-level). Write a SQL query to find the total material cost for each component that appears as a parent, by recursively exploding all nested sub-components and multiplying quantities. Return (root_id, product_name, total_cost) ordered by root_id. Table: Components
| Column Name | Type | Description |
|---|---|---|
| component_id | INT | Primary key |
| name | VARCHAR | Component name |
| unit_cost | REAL | Cost per unit (0 if it is an assembly) |
Table: BOM
| Column Name | Type | Description |
|---|---|---|
| parent_id | INT | Parent component id |
| child_id | INT | Child component id |
| quantity | INT | Number of child units needed per parent unit |
Examples
Bike = 1×Frame(100) + 2×Wheel(Rim30+Tire20=50 each → 100) + 1×Handlebar(15) = 215. Wheel = Rim(30)+Tire(20) = 50. The explosion multiplies quantities at every level.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.