Write a SQL query to analyze weekly shipping performance by facility, including total orders shipped, on-time shipment rate, and week-over-week change.
Use the provided facility and shipment data. Treat a shipment as on time when its shipped timestamp is on or before its promised timestamp. Exclude shipments without a matching facility from the results.
facility_name, week_start, total_orders_shipped, on_time_shipment_rate, and week_over_week_change.NULL when no prior week exists for that facility.facility_name, then week_start.| Column | Type | Description |
|---|---|---|
| facility_idPK | INT | Unique facility identifier |
| facility_code | VARCHAR(20) | Short facility code |
| facility_name | VARCHAR(100) | Facility display name |
| region | VARCHAR(50) | Operating region |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| facility_id | INT | Facility responsible for the shipment |
| shipped_at | TIMESTAMP | Timestamp when the order shipped |
| promised_at | TIMESTAMP | Promised shipment timestamp |