Reverse Bill of Materials — Where Used? (Spoke)
Walk UPWARD from component_id 7 (Spoke) through bill_of_materials to find every product (and intermediate sub-assembly) that USES Spoke. Return product_id, product_name, level — ordered by level, product_id.
- Recursive CTE
- Joins
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Supply chain impact analyst A component change review needs every assembly that directly or indirectly uses a selected spoke.
Return
- Return product identity and upward traversal level.
- Order by level and product_id.
Constraints
- Anchor on component_id 7.
- Reverse the bill-of-materials relationship in 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_nameTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Reverse BoM: anchor on the leaf component, climb to the products that consume it.
Hint 2
Recursive step: bom.component_id = wu.product_id (the next level treats the previous product as a component).
Hint 3
Spoke (7) → Wheel (3) → Bicycle (1).
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE where_used AS (SELECT product_id, product_name, 1 AS level FROM bill_of_materials WHERE component_id = 7 UNION ALL SELECT bom.product_id, bom.product_name, wu.level + 1 FROM bill_of_materials bom INNER JOIN where_used wu ON bom.component_id = wu.product_id) SELECT DISTINCT product_id, product_name, level FROM where_used ORDER BY level, product_id;Why this works
The 'where-used' query is the second-most-asked BoM question (after explosion). It tells engineering teams what to recall, requalify, or recost when a single component changes. The recursion is identical in shape to Q11 (forward BoM) but with the JOIN direction flipped.
Success check
Every direct and indirect parent assembly of the Spoke appears once at the correct level.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| product_id | product_name | level |
|---|---|---|
| 3 | Wheel | 1 |
| 1 | Bicycle | 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.