Delivery Performance - On-Time vs Late per City per Month
Preview mode. Log in to edit, run, submit, and save progress.
Delivery Performance - On-Time vs Late per City per Month
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 |
Examples
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.