Bill of Materials - Recursive Cost Explosion

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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

Database Schema (Inferred)

Components

Column Name	Example Value
component_id	1
name	Bike
unit_cost	0

BOM

Column Name	Example Value
parent_id	1
child_id	2
quantity	1

Example

Components

component_id	name	unit_cost
1	Bike	0
2	Frame	100
3	Wheel	0
4	Rim	30
5	Tire	20
6	Handlebar	15

BOM

parent_id	child_id	quantity
1	2	1
1	3	2
1	6	1
3	4	1
3	5	1

Output

root_id	product_name	total_cost
1	Bike	215
3	Wheel	50

Explanation:

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.