📦 One Stockout Triggers a Chain Reaction.
At Flipkart's supply chain command centre, products aren't built in isolation — they sit inside a bill-of-materials hierarchy, where a finished product is assembled from components, which are themselves assembled from raw materials. When a single raw material runs out, everything above it in the chain — directly or indirectly — is suddenly at risk.
Your task: Given that raw material with component_id = 1 is out of stock, find all finished products (level = 'product') that are impacted — i.e., they depend on component_id = 1 at any level of the hierarchy. Return product_id, product_name, and dependency_depth (how many levels away from the raw material). Order by dependency_depth, product_id.
Use a recursive CTE that walks UP the bill of materials from the raw material.
bill_of_materials Table:
| Column Name | Type |
|---|---|
| component_id | integer |
| component_name | text |
| level | text |
| parent_id | integer |
Expected Outcome (Illustrative Sample):
| product_id | product_name | dependency_depth |
|---|---|---|
| 6 | Steel Cabinet | 3 |
Trace the chain before the warehouse floor finds out the hard way.