Task
You are asked to structure and query a small delivery analytics model that links orders, weather observations, and completed deliveries. Write a PostgreSQL query that returns the average delivery time in minutes for each weather condition, along with the number of completed deliveries used in the calculation. Exclude deliveries that are not completed or that do not have a matching weather observation at the delivery time.
Schema
| Table | Column | Type | Description |
|---|
deliveries | delivery_id | INT | Primary key for each delivery |
deliveries | order_id | INT | Order identifier |
deliveries | driver_id | INT | Driver identifier |
deliveries | pickup_ts | TIMESTAMP | Pickup timestamp |
deliveries | dropoff_ts | TIMESTAMP | Dropoff timestamp |
deliveries | delivery_status | VARCHAR(20) | Delivery status such as completed or canceled |
weather_observations | weather_id | INT | Primary key for each weather record |
weather_observations | observed_at | TIMESTAMP | Weather observation timestamp |
weather_observations | weather_condition | VARCHAR(30) | Condition at the observation time |
weather_observations | temperature_c | NUMERIC(5,2) | Temperature in Celsius |
weather_observations | precipitation_mm | NUMERIC(6,2) | Rain or snow amount |
weather_observations | city | VARCHAR(50) | City name |
Sample data
deliveries
| delivery_id | order_id | driver_id | pickup_ts | dropoff_ts | delivery_status |
|---|
| 1 | 1001 | 501 | 2024-01-02 08:00:00 | 2024-01-02 08:28:00 | completed |
| 2 | 1002 | 502 | 2024-01-02 09:10:00 | 2024-01-02 09:55:00 | completed |
| 3 | 1003 | 503 | 2024-01-02 10:00:00 | 2024-01-02 10:40:00 | canceled |
| 4 | 1004 | 504 | 2024-01-03 11:15:00 | 2024-01-03 11:52:00 | completed |
| 5 | 1005 | 505 | 2024-01-03 12:05:00 | 2024-01-03 12:33:00 | completed |
| 6 | 1006 | 506 | 2024-01-03 13:00:00 | 2024-01-03 13:20:00 | completed |
| 7 | 1007 | 507 | 2024-01-04 14:00:00 | 2024-01-04 14:50:00 | completed |
| 8 | 1008 | 508 | 2024-01-04 15:10:00 | 2024-01-04 15:35:00 | completed |
weather_observations
| weather_id | observed_at | weather_condition | temperature_c | precipitation_mm | city |
|---|
| 11 | 2024-01-02 08:00:00 | clear | 7.20 | 0.00 | San Francisco |
| 12 | 2024-01-02 09:00:00 | rain | 6.10 | 3.40 | San Francisco |
| 13 | 2024-01-03 11:00:00 | snow | -1.50 | 8.20 | San Francisco |
| 14 | 2024-01-03 12:00:00 | snow | -1.20 | 9.10 | San Francisco |
| 15 | 2024-01-04 14:00:00 | clear | 8.00 | 0.00 | San Francisco |
| 16 | 2024-01-04 15:00:00 | fog | 7.40 | 0.00 | San Francisco |
| 17 | 2024-01-05 08:00:00 | rain | 5.80 | 2.00 | San Francisco |
| 18 | 2024-01-03 10:00:00 | clear | 0.50 | 0.00 | San Francisco |
Expected output
| weather_condition | completed_deliveries | avg_delivery_minutes |
|---|
| clear | 3 | 29.33 |
| fog | 1 | 25.00 |
| rain | 1 | 45.00 |
| snow | 2 | 32.50 |
