Deviopath › PostgreSQL

Bill of Materials Cost

Hard · Recursive Queries

A bill of materials says what an item is made of. Assemblies are made of parts, and some of those parts are assemblies themselves. A recursive CTE can "explode" an assembly into all its basic parts, multiplying the quantities along the way: 2 wheels with 32 spokes each means 64 spokes.

components says that assembly_id needs quantity units of part_id. Basic parts have a unit_cost; assemblies have unit_cost = NULL and appear in components as assembly_id. The same part can be used by several assemblies.

For every assembly, return assembly (its name) and total_cost: the cost of all basic parts needed to build one unit of it, at any depth (sum of total quantity × unit_cost). Sort by assembly.

Next: Monthly Cohort Retention