Delivery Performance - On-Time vs Late per City per Month
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a table of deliveries. Each delivery has a city, a promised delivery date, an actual delivery date, and the package weight in kg. A delivery is considered on-time if the actual_date is less than or equal to the promised_date; otherwise it is late. Write a SQL query to find for each month and city: the total number of deliveries, the number of on-time deliveries, the number of late deliveries, and the total weight of all packages (rounded to 2 decimal places). Return the result ordered by month and city. Table: Deliveries
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| city | VARCHAR | Destination city |
| promised_date | DATE | Promised delivery date |
| actual_date | DATE | Actual delivery date |
| weight_kg | REAL | Package weight in kilograms |
Database Schema (Inferred)
Deliveries
| Column Name | Example Value |
|---|---|
| id | 1 |
| city | Mumbai |
| promised_date | 2023-06-05 |
| actual_date | 2023-06-05 |
| weight_kg | 2.5 |
Example
Deliveries
| id | city | promised_date | actual_date | weight_kg |
|---|---|---|---|---|
| 1 | Mumbai | 2023-06-05 | 2023-06-05 | 2.5 |
| 2 | Mumbai | 2023-06-12 | 2023-06-14 | 1 |
| 3 | Delhi | 2023-06-08 | 2023-06-07 | 3 |
| 4 | Delhi | 2023-06-20 | 2023-06-25 | 0.5 |
| 5 | Mumbai | 2023-07-03 | 2023-07-03 | 4 |
| 6 | Delhi | 2023-07-15 | 2023-07-16 | 2 |
Output
| month | city | total_deliveries | on_time_count | late_count | total_weight_kg |
|---|---|---|---|---|---|
| 2023-06 | Delhi | 2 | 1 | 1 | 3.5 |
| 2023-06 | Mumbai | 2 | 1 | 1 | 3.5 |
| 2023-07 | Delhi | 1 | 0 | 1 | 2 |
| 2023-07 | Mumbai | 1 | 1 | 0 | 4 |
Explanation:
Group by month (from promised_date) and city. A delivery is on-time if actual_date <= promised_date. Use CASE WHEN for conditional counting and SUM(weight_kg) for total weight.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Deliveries
| id | city | promised_date | actual_date | weight_kg |
|---|---|---|---|---|
| 1 | Mumbai | 2023-06-05 | 2023-06-05 | 2.5 |
| 2 | Mumbai | 2023-06-12 | 2023-06-14 | 1 |
| 3 | Delhi | 2023-06-08 | 2023-06-07 | 3 |
| 4 | Delhi | 2023-06-20 | 2023-06-25 | 0.5 |
| 5 | Mumbai | 2023-07-03 | 2023-07-03 | 4 |
| 6 | Delhi | 2023-07-15 | 2023-07-16 | 2 |
Output
| month | city | total_deliveries | on_time_count | late_count | total_weight_kg |
|---|---|---|---|---|---|
| 2023-06 | Delhi | 2 | 1 | 1 | 3.5 |
| 2023-06 | Mumbai | 2 | 1 | 1 | 3.5 |
| 2023-07 | Delhi | 1 | 0 | 1 | 2 |
| 2023-07 | Mumbai | 1 | 1 | 0 | 4 |