Scenario SQL Question

Calculate Total Cost of Product with Sub-Components

Build mastery in Self Joins Hierarchical with this scenario-based SQL challenge and live execution support.

Problem Statement

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.

Relevant Tables

bill_of_materialscomponents

SQL Concepts Used in This Scenario

Strengthen your understanding with these targeted learning topics:

Self JoinsJoins & Relationships
Recursive CTEs for Hierarchical DataSubqueries & CTEs
Common Table ExpressionsSubqueries & CTEs