Delivery SLA Breaches by Postal Zone — SQL Dates Problem
The logistics team tracks how often couriers miss the promised delivery time.
- Difficulty: Medium
- Topics: Dates, Strings, Aggregation
- Dialect: MySQL
- Problem: #110
Problem statement
The logistics team tracks how often couriers miss the promised delivery time. The first digit of an Indian PIN code is its postal zone (1 = Delhi and the north, 4 = the west, 5 = Andhra/Telangana/Karnataka, and so on). A delivered shipment breaches its SLA when the days from shipped_on to delivered_on are more than promised_days. Shipments not yet delivered are ignored.
Return one row per zone with at least one delivered shipment: zone (the first digit), delivered and breaches. Order by breaches descending, then zone ascending.
Tables
Table: shipments
| Column | Type |
|---|---|
| shipment_id | int |
| order_id | int |
| pincode | char |
| shipped_on | date |
| promised_days | int |
| delivered_on | date |
Primary key: shipment_id.
One row per shipment; pincode is the six-digit delivery PIN code as text. delivered_on is NULL while in transit.
Examples
Example 1
shipments
| shipment_id | order_id | pincode | shipped_on | promised_days | delivered_on |
|---|---|---|---|---|---|
| 1 | 9101 | 560034 | 2024-10-01 | 2 | 2024-10-03 |
| 2 | 9102 | 560102 | 2024-10-01 | 2 | 2024-10-04 |
| 3 | 9103 | 400076 | 2024-10-02 | 3 | 2024-10-07 |
| 4 | 9104 | 411014 | 2024-10-02 | 3 | 2024-10-06 |
| 5 | 9105 | 110017 | 2024-10-03 | 4 | 2024-10-05 |
| 6 | 9106 | 500081 | 2024-10-03 | 2 | NULL |
| 7 | 9107 | 600042 | 2024-10-04 | 3 | 2024-10-08 |
| 8 | 9108 | 122002 | 2024-10-04 | 4 | 2024-10-08 |
Output
| zone | delivered | breaches |
|---|---|---|
| 4 | 2 | 2 |
| 5 | 2 | 1 |
| 6 | 1 | 1 |
| 1 | 2 | 0 |
How to solve Delivery SLA Breaches by Postal Zone
The zone is a string operation on the PIN code — its first character, LEFT(pincode, 1) — and it is the grouping key. Because PIN codes are stored as text, there is no arithmetic involved, and a leading digit is never lost the way it could be if the code were a number.
A shipment breaches when DATEDIFF(delivered_on, shipped_on) > promised_days; delivering on exactly the promised day is on time (shipment 4 in the example takes 4 days against 3 promised and breaches; shipment 1 takes 2 against 2 and does not). The breach count is a conditional SUM, and the delivered count is the group size once the in-transit rows (NULL delivered_on) are filtered out in WHERE. A zone whose only shipment is still in transit has no group and is not listed.
The alternative states the SLA as a date comparison: late when delivered_on is after shipped_on plus the promised days. The two are the same rule. One pass over the shipments, then a sort of at most ten zones.
Reference solution (MySQL)
SELECT LEFT(pincode, 1) AS zone,
COUNT(*) AS delivered,
SUM(CASE WHEN DATEDIFF(delivered_on, shipped_on) > promised_days THEN 1 ELSE 0 END) AS breaches
FROM shipments
WHERE delivered_on IS NOT NULL
GROUP BY LEFT(pincode, 1)
ORDER BY breaches DESC, zoneAnother way
SELECT zone, COUNT(*) AS delivered, SUM(late) AS breaches
FROM (SELECT SUBSTRING(pincode, 1, 1) AS zone, IF(delivered_on > DATE_ADD(shipped_on, INTERVAL promised_days DAY), 1, 0) AS late
FROM shipments WHERE delivered_on IS NOT NULL) t
GROUP BY zone
ORDER BY breaches DESC, zone← Customers Whose First Order Used a Coupon · Flash Sale Units Sold Inside the Sale Window →