Interview Questions/SQL/Recursive Bill of Materials - Full Component Explosion

Recursive Bill of Materials - Full Component Explosion

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

Medium

Recursive Bill of Materials - Full Component Explosion

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

Examples

Example 1

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.

query.sql