Bill of Materials

Hook: A Bill of Materials is just a product family tree, and interviewers love it because one small part can cascade into many needed parts.

Question: How do you write a SQL query to explode a Bill of Materials and calculate the total quantity needed for each component?

Answer: A Bill of Materials, or BOM, is a table that says which parts make up a parent part and how many of each child part are needed. The usual SQL solution is a recursive CTE, which means a Common Table Expression that can call itself until no more child rows are found. You start with the top product, then repeatedly join to its children and multiply quantities along the path.

Interview-Ready Answer: I would solve a BOM in SQL with a recursive CTE. First I anchor on the finished product, then I join each part to its children and multiply quantities as I go down the tree. That gives me the full exploded structure, and then I group by component to get the total required amount. I would also guard against cycles, because a bad BOM row can otherwise cause infinite recursion.

What a BOM really means

A Bill of Materials is a hierarchy. Think of a bike: the bike contains a frame, wheels, and a seat; the wheels contain rims, spokes, and hubs; the frame may contain bolts and paint. The key idea is that the quantity of a leaf part is the product of all quantities along the path.

How the recursive CTE works under the hood

1. Seed the tree. Start with the top-level item, often called the anchor row. This is the first result set the CTE returns.
2. Join to direct children. The recursive part joins the current level to the BOM table where the current component becomes the next parent.
3. Multiply quantities. If one bike needs 2 wheels and each wheel needs 28 spokes, the bike needs 56 spokes. That multiplication happens on every step.
4. Repeat until no rows remain. The database keeps feeding the new rows back into the recursive term until a pass returns zero rows.
5. Aggregate at the end. If the same component appears through multiple paths, sum the quantities after recursion finishes.

Why recursive CTE beats a plain subquery

A normal subquery can fetch one level of children, but it cannot naturally keep descending through unknown depth. A recursive CTE is built for trees and graphs. That is why BOMs, org charts, folder structures, and category trees almost always use it.

Important details interviewers probe

• Use UNION ALL, not UNION. UNION ALL keeps every row and is faster because it skips duplicate elimination. In a BOM, duplicate paths often matter because they represent real quantity.
• Index the parent key. A B-tree index on parent_part is the usual choice. Without it, each recursion level may scan the whole BOM table again.
• Watch recursion limits. Some engines protect you with defaults: SQL Server defaults to 100 levels unless you change MAXRECURSION; MySQL 8 defaults to 1000 via cte_max_recursion_depth. PostgreSQL does not use a small fixed default in the same way, but it still stops when no more rows are produced.
• Detect cycles. A bad row like part A containing itself can loop forever unless you track a path or enforce data rules.

Performance and complexity

For a valid tree, the work is roughly proportional to the number of exploded edges, so think O(nodes + edges) for the result you actually materialize. In practice, the expensive part is not the math; it is repeated joins and, if you add cycle checks with a path string, repeated membership tests. A BOM with 10,000 edges is usually fine with indexing, but a highly shared structure can create many output rows because one component may appear under several branches.

Edge cases to remember

• Shared components: the same part may appear in multiple subassemblies, so aggregate after recursion.
• Missing child rows: if a component has no children, it is a leaf and recursion naturally stops.
• Cycles: never assume the data is perfect; protect the query or the table.
• Lead-time or cost columns: you can multiply or sum them too, but be clear whether the metric is per path or per final part.

Memory line: Seed the top, walk downward, multiply on the way, sum at the end.

Real-world story

Imagine a manufacturing planning system for an e-bike company. A checkout service sells one bike, but the warehouse must reserve frame tubes, wheels, spokes, bolts, batteries, and packaging. The BOM query feeds the inventory reservation service so it knows the exact parts to deduct from stock.

What goes wrong if someone writes only a one-level query? The system reserves the bike shell and the wheels, but forgets the spokes, bolts, and battery cells hidden deeper in the tree. At peak traffic, orders look successful, but the line later stalls because the warehouse runs out of parts it never reserved. The symptom is subtle: customer orders are accepted, yet manufacturing exceptions appear hours later with logs like insufficient_component_stock or reservation_shortfall.

That is why BOM bugs are expensive. They do not fail loudly; they fail by undercounting. A correct recursive CTE turns a hidden dependency tree into a flat, trustworthy list the rest of the system can use.

Follow-up & Tricky Questions:

• How do you prevent cycles in a BOM? Track the visited path, use a depth cap, or enforce data integrity so a part cannot eventually contain itself. The safest answer is to do both query-side protection and table-side validation.
• Why do we multiply quantities instead of adding them during recursion? Because each level represents a nested requirement. If one parent needs 2 children and each child needs 3 of a leaf, the leaf requirement is 2 × 3 = 6.
• How would you include total cost? Carry a unit cost column in the recursive CTE, then multiply cost by quantity at each step and sum at the end. Be careful to define whether the cost lives on leaves only or on every node.
• How do you get only leaf parts? Add a final filter for parts that never appear as a parent, or join to a list of parents and keep rows where no children exist. That is useful when the warehouse only needs raw materials.
• What is the difference between a recursive CTE and a closure table? A recursive CTE computes the tree on demand; a closure table stores all ancestor-descendant pairs ahead of time. Closure tables read faster but cost more to maintain on inserts and updates.
• Why not use UNION instead of UNION ALL? UNION removes duplicates and can accidentally hide valid repeated paths, plus it adds sort or hash work. In BOM queries, repeated components often represent real demand, so UNION ALL is the correct default.
• Can a recursive CTE aggregate inside the recursive term? Usually you keep the recursion simple and aggregate after the CTE finishes. That is easier to reason about and avoids fighting engine-specific restrictions.
• Gotcha: if a part appears in two branches, do you count it twice? Yes, if the business rule says each occurrence requires a new unit. If the part is a shared reference that should only be reserved once, that is a different modeling problem, not a SQL bug.

Common Mistakes:

• Using only one join level. Correction: use a recursive CTE so the query keeps descending until the leaves.
• Adding quantities instead of multiplying along the path. Correction: multiply inside recursion, then sum per component at the end.
• Forgetting cycle protection. Correction: guard with a visited path or enforce acyclic data.
• Using UNION by habit. Correction: prefer UNION ALL unless you explicitly need duplicate removal.

Memory Hook: Picture a tree of nested shipping boxes: open the top box, find the smaller boxes inside, multiply the labels as you go, then count all the items at the bottom.

Cheat Sheet:

• Anchor = starting product.
• Recursive step = join children to current component.
• Multiply quantities on each edge.
• Use UNION ALL.
• Group after recursion for totals.
• Protect against cycles and deep trees.

Practice Tasks:

• Write a query that returns only leaf components for the bike BOM.
• Add a unit_cost column and compute total material cost for the final product.
• Modify the recursive CTE to return the full path and level for each component.