Task
You are given a table of Instacart delivery records. Write a SQL query to find the busiest hour of the day for deliveries based on the number of completed deliveries. Return the hour of day and the total number of deliveries in that hour. If multiple hours tie, return the earliest hour.
Schema
| column_name | type | description |
|---|
| delivery_id | INT | Unique delivery identifier |
| shopper_id | INT | Shopper assigned to the delivery |
| delivered_at | TIMESTAMP | Timestamp when the delivery was completed |
| delivery_status | VARCHAR(20) | Delivery status such as completed, canceled, or in_transit |
| batch_id | INT | Batch identifier for grouped deliveries |
Sample data
| delivery_id | shopper_id | delivered_at | delivery_status | batch_id |
|---|
| 104 | 12 | 2024-06-01 18:05:00 | completed | 9003 |
| 101 | 10 | 2024-06-01 09:15:00 | completed | 9001 |
| 106 | 14 | 2024-06-01 09:45:00 | canceled | 9004 |
| 102 | 11 | 2024-06-01 09:40:00 | completed | 9001 |
| 103 | 10 | 2024-06-01 14:10:00 | completed | 9002 |
Expected output
| delivery_hour | total_deliveries |
|---|
| 9 | 3 |
