Skip to content
RED SAGSkills
hard60 ptsRecursive CteAggregation

Bill of Materials Explosion

components describes how assemblies are built: each row says parent needs qty units of child. A child can itself be an assembly, and the same part can be reached through several paths. There are no cycles.

A finished product is a part that appears as a parent but never as a child. A raw part is a part that appears as a child but never as a parent. For each finished product, compute how many units of each raw part it needs in total (multiply quantities along each path, then add up the paths).

Return columns product, raw_part, total_qty. Order by product ascending, then raw_part ascending.

Schema

CREATE TABLE components (
  parent TEXT NOT NULL,
  child TEXT NOT NULL,
  qty INTEGER NOT NULL,
  PRIMARY KEY (parent, child)
);

Sample data

components

parentchildqty
bikewheel2
bikeframe1
wheelspoke32
wheelrim1
wheeltube1
frametube3
framebolt4
Hint 1

Anchor on edges whose parent is never a child. Those are the finished products.

Hint 2

Each recursive step moves one level down and multiplies the quantity by the next edge's qty.

Hint 3

At the end keep only raw parts and SUM, because one part can be reached through several branches.

Good practice for: Data Engineer, Backend Developer, Database Administrator, Data Analyst

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.