Write a SQL query to track weekly logistics KPIs by site, including shipments, on-time delivery rate, and backlog, and compare them to the prior week.
Use the sites and shipments tables. Treat delivered shipments as on time when delivered_at is on or before promised_at; calculate the rate among delivered shipments and treat non-delivered shipments as backlog.
site_name, week_start, shipment_count, on_time_delivery_rate, backlog_count, and prior-week equivalents for all three KPIs.site_id and week_start.| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique logistics site identifier |
| site_name | VARCHAR(100) | Logistics site name |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| site_id | INT | Site handling the shipment |
| shipment_date | DATE | Date the shipment entered the reporting workflow |
| status | VARCHAR(30) | Current shipment status |
| promised_at | DATE | Promised delivery date |
| delivered_at | DATE | Actual delivery date |