Identify Supply Chain Bottlenecks by Delay Rate

You are given shipment data across suppliers, warehouses, and deliveries. Write a PostgreSQL query that identifies the supply chain stages with the highest bottleneck risk by comparing planned and actual dates, then aggregating delay rates by stage. Return only stages with at least 3 shipments and a delay rate above 30%, ordered by delay rate descending.

Schema

shipments

Column	Type	Description
shipment_idPK	INT	Primary key for each shipment
supplier_id	INT	References the supplier that shipped the goods
warehouse_id	INT	References the warehouse handling the shipment
planned_ship_date	DATE	Planned outbound shipment date
actual_ship_date	DATE	Actual outbound shipment date, if shipped
stage	VARCHAR(50)	Supply chain stage name

suppliers

Column	Type	Description
supplier_idPK	INT	Primary key for each supplier
supplier_name	VARCHAR(100)	Supplier display name

warehouses

Column	Type	Description
warehouse_idPK	INT	Primary key for each warehouse
warehouse_name	VARCHAR(100)	Warehouse display name

Your question is Identify Supply Chain Bottlenecks by Delay Rate. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem