Write a SQL query to calculate weekly delivery success rate, average turnaround time, and failure counts by site from operational tables for Zipline.
Use requested_at to determine the delivery week. Treat delivered as successful and calculate turnaround time only for successful deliveries. Include each site-week with at least one delivery attempt.
site_name, week_start, success_rate, average_turnaround_minutes, and failure_count.success_rate as a percentage rounded to two decimals and turnaround time in minutes rounded to two decimals.week_start ascending, then site_name ascending.| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique identifier for a Zipline operating site |
| site_name | VARCHAR(100) | Human-readable operating site name |
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Unique identifier for a delivery attempt |
| site_id | INT | Operating site associated with the delivery attempt |
| requested_at | TIMESTAMP | Timestamp when the delivery was requested |
| delivered_at | TIMESTAMP | Timestamp when the delivery was completed |
| status | VARCHAR(20) | Delivery outcome, either delivered or failed |
| failure_reason | VARCHAR(100) | Reason recorded when a delivery fails |