Total Cost of Product Including All Sub-Components
Recursive CTE on bill_of_materials joined to components to calculate extended_cost (unit_cost × cumulative_quantity) for product_id 1 (Bicycle). Return product_id, product_name, component_id, component_name, unit_cost, extended_cost, level — ordered by level, component_id.
- Recursive CTE
- CTEs
- Joins
- Subqueries
- Numeric functions
Exercise brief
Understand the request
Manufacturing cost analyst The product costing model must carry cumulative component quantity before applying unit cost.
Return
- Return component identity, unit cost, extended cost, and level.
- Order by level and component_id.
Constraints
- Join the component catalog at each level.
- Calculate cost from cumulative quantity, not local quantity.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
bill_of_materials
product_idINTEGERproduct_nameTEXTcomponent_idINTEGERcomponent_nameTEXTquantityINTEGER
components
component_idINTEGERcomponent_nameTEXTunit_costDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Carry a cumulative_quantity column through the recursion — that is what extends costs correctly.
Hint 2
JOIN to components inside both the anchor and the recursive step to get unit_cost.
Hint 3
Wrap money values with ROUND(..., 2) — works on every engine.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE bom_cost AS (SELECT bom.product_id, bom.product_name, bom.component_id, bom.component_name, c.unit_cost, bom.quantity AS cum_qty, ROUND(c.unit_cost * bom.quantity, 2) AS extended_cost, 1 AS level FROM bill_of_materials bom INNER JOIN components c ON bom.component_id = c.component_id WHERE bom.product_id = 1 UNION ALL SELECT bc.product_id, bc.product_name, bom.component_id, bom.component_name, c.unit_cost, bc.cum_qty * bom.quantity AS cum_qty, ROUND(c.unit_cost * bc.cum_qty * bom.quantity, 2) AS extended_cost, bc.level + 1 FROM bill_of_materials bom INNER JOIN bom_cost bc ON bom.product_id = bc.component_id INNER JOIN components c ON bom.component_id = c.component_id) SELECT product_id, product_name, component_id, component_name, unit_cost, extended_cost, level FROM bom_cost ORDER BY level, component_id;Why this works
Costing a BoM = "total cost of a product" requires summing extended_cost across all leaves. The cumulative-quantity column is the trick: every leaf contributes unit_cost × (quantity multiplied through the chain).
Success check
Every component cost reflects the quantity required by the top-level Bicycle.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| product_id | product_name | component_id | component_name | unit_cost | extended_cost | level |
|---|---|---|---|---|---|---|
| 1 | Bicycle | 2 | Frame | 150 | 150 | 1 |
| 1 | Bicycle | 3 | Wheel | 75 | 150 | 1 |
| 1 | Bicycle | 4 | Steel Tube | 25 | 75 | 2 |
| 1 | Bicycle | 5 | Weld Joint | 5 | 30 | 2 |
| 1 | Bicycle | 6 | Rim | 30 | 60 | 2 |
| 1 | Bicycle | 7 | Spoke | 0.5 | 32 | 2 |
| 1 | Bicycle | 8 | Hub | 15 | 30 | 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.