Write a SQL query to find the on-time delivery rate by facility and week from shipment and scan tables.
Use the shipment's promised delivery date to determine its week. A shipment is on time when its earliest delivery scan occurs on or before the promised date. Shipments without a delivery scan count as not on time.
facility_id, week_start, shipment_count, on_time_shipments, and on_time_rate.facility_id and week_start.| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| facility_id | VARCHAR(10) | UPS facility responsible for the shipment |
| promised_delivery_date | DATE | Date by which the shipment is expected to be delivered |
| Column | Type | Description |
|---|---|---|
| scan_idPK | INT | Unique scan identifier |
| shipment_id | INT | Shipment associated with the scan |
| scan_type | VARCHAR(20) | Operational scan event type |
| scan_time | TIMESTAMP | Timestamp when the scan was recorded |