Your question is Rank Suppliers by Missed Windows. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given shipment planning and receipt data from Lucid Motors supplier operations. Write a SQL query to identify which suppliers most frequently miss their delivery windows for shipments scheduled in March 2024. A shipment counts as missed if the actual arrival timestamp is before the scheduled window start, after the scheduled window end, or missing entirely because nothing was received. Return each supplier's total scheduled shipments, number of missed shipments, and missed-window rate, ordered from worst to best.
| Column | Type | Description |
|---|---|---|
| supplier_idPK | INT | Unique supplier identifier |
| supplier_name | VARCHAR(100) | Supplier name |
| region | VARCHAR(50) | Supplier region |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| supplier_id | INT | Supplier linked to the shipment |
| lucid_plant | VARCHAR(50) | Lucid Motors receiving site |
| scheduled_window_start | TIMESTAMP | Scheduled delivery window start |
| scheduled_window_end | TIMESTAMP | Scheduled delivery window end |
| Column | Type | Description |
|---|---|---|
| receipt_idPK | INT | Unique receipt event identifier |
| shipment_id | INT | Shipment tied to the receipt |
| actual_arrival_ts | TIMESTAMP | Actual arrival timestamp |