Couriers Who Missed the Delivery SLA on Over 20% of May Drops
Each delivery carries a promised_by time; a delivery is late when delivered_at is after promised_by.
- Difficulty: Medium
- Topics: Aggregation, Dates, Joins
- Dialect: MySQL
- Problem: #307
Problem statement
Each delivery carries a promised_by time; a delivery is late when delivered_at is after promised_by. The last-mile manager reviews couriers whose late share in May 2024 (deliveries whose delivered_at falls in May 2024) is strictly more than 20%. Failed deliveries have no delivered_at and are ignored.
Return courier_id, name, deliveries (May deliveries), late_deliveries and late_pct (late ÷ deliveries × 100, rounded to 2 decimals). Order by late_pct descending, then courier_id ascending.
Tables
Table: Courier
| Column | Type |
|---|---|
| courier_id | int |
| name | varchar |
| hub | varchar |
Primary key: courier_id.
One row per last-mile delivery associate.
Table: Delivery
| Column | Type |
|---|---|
| delivery_id | int |
| courier_id | int |
| promised_by | datetime |
| delivered_at | datetime |
| status | enum(delivered, failed) |
Primary key: delivery_id.
delivered_at is NULL for a failed attempt. A delivery exactly at promised_by is on time.
Examples
Example 1
Courier
| courier_id | name | hub |
|---|---|---|
| 1 | Mahesh | HSR Layout |
| 2 | Anwar | Andheri East |
| 3 | Selvi | Velachery |
Delivery
| delivery_id | courier_id | promised_by | delivered_at | status |
|---|---|---|---|---|
| 1 | 1 | 2024-05-02 18:00:00 | 2024-05-02 18:40:00 | delivered |
| 2 | 1 | 2024-05-03 14:00:00 | 2024-05-03 13:10:00 | delivered |
| 3 | 1 | 2024-05-04 12:00:00 | 2024-05-04 12:00:00 | delivered |
| 4 | 1 | 2024-05-05 20:00:00 | NULL | failed |
| 5 | 2 | 2024-05-10 11:00:00 | 2024-05-10 10:30:00 | delivered |
| 6 | 2 | 2024-05-11 11:00:00 | 2024-05-11 12:15:00 | delivered |
| 7 | 2 | 2024-05-12 11:00:00 | 2024-05-12 10:00:00 | delivered |
| 8 | 2 | 2024-05-13 11:00:00 | 2024-05-13 10:45:00 | delivered |
| 9 | 2 | 2024-05-14 11:00:00 | 2024-05-14 10:50:00 | delivered |
| 10 | 3 | 2024-04-30 21:00:00 | 2024-05-01 09:30:00 | delivered |
| 11 | 3 | 2024-05-31 22:00:00 | 2024-06-01 08:00:00 | delivered |
Output
| courier_id | name | deliveries | late_deliveries | late_pct |
|---|---|---|---|---|
| 3 | Selvi | 1 | 1 | 100 |
| 1 | Mahesh | 3 | 1 | 33.33 |
How to solve Couriers Who Missed the Delivery SLA on Over 20% of May Drops
First decide which rows are in scope: deliveries whose delivered_at falls in May 2024, written as >= '2024-05-01' and < '2024-06-01'. That one filter also removes failed attempts, because a NULL delivered_at fails every comparison. A parcel promised on 30 April but dropped on 1 May is a May delivery; one dropped on 1 June is not.
Then group per courier and compute two numbers in one pass: COUNT(*) for the deliveries and SUM(CASE WHEN delivered_at > promised_by THEN 1 ELSE 0 END) for the late ones. A drop exactly at the promised time is on time, so the comparison is strict.
The threshold compares aggregates, so it is a HAVING. Writing it as 5 * late > deliveries keeps it in integers — no rounding can push 20.0000 % over the line — while late / deliveries > 0.2 is equivalent in exact arithmetic. The percentage itself is rounded to two decimals for display. The cost is one scan of the month's deliveries plus a join to the small courier table.
Reference solution (MySQL)
SELECT c.courier_id, c.name,
COUNT(*) AS deliveries,
SUM(CASE WHEN d.delivered_at > d.promised_by THEN 1 ELSE 0 END) AS late_deliveries,
ROUND(100 * SUM(CASE WHEN d.delivered_at > d.promised_by THEN 1 ELSE 0 END) / COUNT(*), 2) AS late_pct
FROM Courier c
JOIN Delivery d ON d.courier_id = c.courier_id
WHERE d.delivered_at >= '2024-05-01' AND d.delivered_at < '2024-06-01'
GROUP BY c.courier_id, c.name
HAVING 5 * SUM(CASE WHEN d.delivered_at > d.promised_by THEN 1 ELSE 0 END) > COUNT(*)
ORDER BY late_pct DESC, c.courier_idAnother way
WITH may AS (
SELECT courier_id, COUNT(*) AS deliveries, SUM(IF(delivered_at > promised_by, 1, 0)) AS late_deliveries
FROM Delivery
WHERE status = 'delivered' AND DATE_FORMAT(delivered_at, '%Y-%m') = '2024-05'
GROUP BY courier_id
)
SELECT c.courier_id, c.name, m.deliveries, m.late_deliveries, ROUND(100 * m.late_deliveries / m.deliveries, 2) AS late_pct
FROM may m JOIN Courier c ON c.courier_id = m.courier_id
WHERE m.late_deliveries / m.deliveries > 0.2
ORDER BY late_pct DESC, c.courier_id← Dispatch Offer Acceptance Rate per Driver · Top-Earning Driver in Each City for Q1 2024 →