Recursive Bill of Materials - Full Component Explosion

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

Medium

Description

You are given a Bill of Materials (BOM) table that describes parent-child relationships between parts. Each row says: to build one unit of parent_id you need `quantity` units of child_id. Relationships can be multiple levels deep. Write a recursive SQL query that, starting from product with id = 1, finds every component at any depth. For each unique component, return its component_id, the total quantity needed (summing across all paths that lead to it), and the maximum depth at which it appears. Return results ordered by component_id. Table: BOM

Column Name	Type	Description
parent_id	INT	The part being built
child_id	INT	The component required
quantity	INT	Units of child needed per unit of parent

Database Schema (Inferred)

BOM

Column Name	Example Value
parent_id	1
child_id	2
quantity	2

Example

BOM

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

Output

component_id	total_quantity	depth
2	2	1
3	1	1
4	6	2
5	4	2
6	1	2
7	6	3

Explanation:

Start from parent_id=1. Recurse: at each step multiply the parent's running quantity by the child's quantity. Component 5 appears via two paths (through 2: 2×1=2, through 3: 1×2=2), so total=4. Component 7 needs 2×3×1=6.

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.