Write a SQL query to analyze weekly production output, defect rate, and on-time shipment performance by plant and product line at Nestl S.A.
Use the provided production, shipment, and plant data. Calculate the requested metrics for every production week and plant-product combination.
week_start, plant_name, product_line, units_produced, defect_rate_pct, and on_time_shipment_rate_pct.week_start, plant_name, and product_line.| Column | Type | Description |
|---|---|---|
| plant_idPK | INT | Unique plant identifier |
| plant_name | VARCHAR(100) | Nestlé plant name |
| Column | Type | Description |
|---|---|---|
| batch_idPK | INT | Unique production batch identifier |
| plant_id | INT | Plant where the batch was produced |
| product_line | VARCHAR(80) | Product line produced by the batch |
| production_date | DATE | Date production was completed |
| units_produced | INT | Units produced in the batch |
| defective_units | INT | Units identified as defective |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| batch_id | INT | Production batch associated with the shipment |
| shipped_date | DATE | Date the shipment left the plant |
| promised_date | DATE | Date by which the shipment was promised |