Undelivered Shipments Past Their Promised Date — SQL Problem
The customer-experience desk runs a breach report every morning.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #304
Problem statement
The customer-experience desk runs a breach report every morning. On the report date 2024-06-15, a shipment is breached when it is still in_transit, has no delivered_on date, and its promised_date is before 2024-06-15. Shipments returned to origin (rto) are handled elsewhere and never appear.
Return awb, promised_date and days_overdue (the days from the promised date to 2024-06-15) for every breached shipment, ordered by days_overdue descending and then awb ascending.
Tables
Table: Shipment
| Column | Type |
|---|---|
| awb | bigint |
| destination_city | varchar |
| promised_date | date |
| delivered_on | date |
| status | enum(in_transit, delivered, rto) |
Primary key: awb.
awb is the 12-digit air waybill number. delivered_on is NULL until the parcel is delivered.
Examples
Example 1
Shipment
| awb | destination_city | promised_date | delivered_on | status |
|---|---|---|---|---|
| 784512300011 | Pune | 2024-06-10 | NULL | in_transit |
| 784512300012 | Mumbai | 2024-06-15 | NULL | in_transit |
| 784512300013 | Delhi | 2024-06-12 | 2024-06-14 | delivered |
| 784512300014 | Chennai | 2024-06-01 | NULL | rto |
| 784512300015 | Kochi | 2024-06-14 | NULL | in_transit |
| 784512300016 | Jaipur | 2024-06-10 | NULL | in_transit |
| 784512300017 | Delhi | 2024-06-20 | NULL | in_transit |
Output
| awb | promised_date | days_overdue |
|---|---|---|
| 784512300011 | 2024-06-10 | 5 |
| 784512300016 | 2024-06-10 | 5 |
| 784512300015 | 2024-06-14 | 1 |
How to solve Undelivered Shipments Past Their Promised Date
Breached shipments are the rows that pass three tests: status = 'in_transit' (RTO and delivered parcels are excluded), delivered_on IS NULL (no delivery recorded) and promised_date < '2024-06-15'. The comparison is strict — a parcel promised for the 15th still has the whole day, so it is not overdue on the morning report.
The overdue age is the gap between two dates, DATEDIFF('2024-06-15', promised_date); the later date goes first so the result is positive. TIMESTAMPDIFF(DAY, promised_date, '2024-06-15') takes its arguments the other way round and gives the same number for plain dates.
Testing IS NULL needs IS, never = NULL, which is never true. The order is oldest breach first, and since many parcels share a promised date the AWB number breaks ties. With an index on (status, promised_date) the report reads only the in-transit rows it needs.
Reference solution (MySQL)
SELECT awb, promised_date, DATEDIFF('2024-06-15', promised_date) AS days_overdue
FROM Shipment
WHERE status = 'in_transit'
AND delivered_on IS NULL
AND promised_date < '2024-06-15'
ORDER BY days_overdue DESC, awbAnother way
SELECT awb, promised_date, TIMESTAMPDIFF(DAY, promised_date, '2024-06-15') AS days_overdue FROM Shipment WHERE status NOT IN ('delivered', 'rto') AND delivered_on IS NULL AND DATEDIFF('2024-06-15', promised_date) > 0 ORDER BY days_overdue DESC, awb ASC← Late-Night Ride Requests for the May Safety Audit · Drivers Rated Above the Fleet Average →