Riders Missing the Delivery SLA in June 2024 — SQL Problem
Every delivery carries a promised time. A delivery is late when the minutes from pickup to drop-off (TIMESTAMPDIFF(MINUTE, picked_up_at, delivered_at)) are…
- Difficulty: Medium
- Topics: Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #156
Problem statement
Every delivery carries a promised time. A delivery is late when the minutes from pickup to drop-off (TIMESTAMPDIFF(MINUTE, picked_up_at, delivered_at)) are greater than promised_minutes. Only deliveries picked up in June 2024 and already completed count; one still on the road has delivered_at NULL and is ignored.
Return the riders whose late deliveries are more than 20% of their counted deliveries (exactly 20% is not more), with columns rider_id, name, deliveries, late_deliveries and late_pct (late as a percentage of deliveries, rounded to 1 decimal place). Return the rows in any order.
Tables
Table: Rider
| Column | Type |
|---|---|
| rider_id | int |
| name | varchar |
| city | varchar |
Primary key: rider_id.
One row per delivery partner.
Table: Delivery
| Column | Type |
|---|---|
| delivery_id | int |
| rider_id | int |
| picked_up_at | datetime |
| delivered_at | datetime |
| promised_minutes | int |
Primary key: delivery_id.
One row per delivery; rider_id is always in Rider. delivered_at is NULL while the order is still out for delivery.
Examples
Example 1
Rider
| rider_id | name | city |
|---|---|---|
| 1 | Arjun | Bengaluru |
| 2 | Pooja | Bengaluru |
| 3 | Kabir | Bengaluru |
Delivery
| delivery_id | rider_id | picked_up_at | delivered_at | promised_minutes |
|---|---|---|---|---|
| 1 | 1 | 2024-06-03 12:10:00 | 2024-06-03 12:40:00 | 25 |
| 2 | 1 | 2024-06-03 19:00:00 | 2024-06-03 19:20:00 | 20 |
| 3 | 1 | 2024-06-04 13:00:00 | 2024-06-04 13:18:00 | 20 |
| 4 | 2 | 2024-06-05 20:00:00 | 2024-06-05 20:22:00 | 20 |
| 5 | 2 | 2024-06-05 21:00:00 | 2024-06-05 21:15:00 | 20 |
| 6 | 2 | 2024-06-06 12:00:00 | 2024-06-06 12:19:00 | 20 |
| 7 | 2 | 2024-06-06 13:00:00 | 2024-06-06 13:20:00 | 20 |
| 8 | 2 | 2024-06-07 19:30:00 | 2024-06-07 19:45:00 | 20 |
| 9 | 3 | 2024-05-31 22:00:00 | 2024-05-31 22:50:00 | 30 |
| 10 | 3 | 2024-06-08 20:00:00 | NULL | 30 |
| 11 | 3 | 2024-06-09 20:00:00 | 2024-06-09 20:31:00 | 30 |
Output
| rider_id | name | deliveries | late_deliveries | late_pct |
|---|---|---|---|---|
| 1 | Arjun | 3 | 1 | 33.3 |
| 3 | Kabir | 1 | 1 | 100 |
How to solve Riders Missing the Delivery SLA in June 2024
First decide which rows count: pickups from 1 June up to (not including) 1 July, and only completed deliveries. Both are row filters, so they go in WHERE. Dropping the NULL delivered_at rows matters twice — they are not deliveries yet, and TIMESTAMPDIFF over a NULL is NULL, which would otherwise sit in the count as neither late nor on time.
Then group by rider. COUNT(*) is the deliveries; conditional aggregation counts the late ones: SUM(CASE WHEN minutes > promised THEN 1 ELSE 0 END). The SLA rule compares two group values, so it belongs in HAVING. "More than 20%" is late / deliveries > 0.2, or with no division at all late * 5 > deliveries — exactly one late delivery in five is 20%, not more, and stays out.
The percentage column is rounded to one decimal as asked; the comparison uses the exact values. A CTE that flags each delivery first and aggregates second is the same plan written in steps. One pass over the deliveries, one group per rider.
Reference solution (MySQL)
SELECT r.rider_id, r.name,
COUNT(*) AS deliveries,
SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, d.picked_up_at, d.delivered_at) > d.promised_minutes THEN 1 ELSE 0 END) AS late_deliveries,
ROUND(100 * SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, d.picked_up_at, d.delivered_at) > d.promised_minutes THEN 1 ELSE 0 END) / COUNT(*), 1) AS late_pct
FROM Rider r
JOIN Delivery d ON d.rider_id = r.rider_id
WHERE d.picked_up_at >= '2024-06-01' AND d.picked_up_at < '2024-07-01'
AND d.delivered_at IS NOT NULL
GROUP BY r.rider_id, r.name
HAVING SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, d.picked_up_at, d.delivered_at) > d.promised_minutes THEN 1 ELSE 0 END) * 5 > COUNT(*)Another way
WITH counted AS (
SELECT rider_id, IF(TIMESTAMPDIFF(MINUTE, picked_up_at, delivered_at) > promised_minutes, 1, 0) AS is_late
FROM Delivery
WHERE YEAR(picked_up_at) = 2024 AND MONTH(picked_up_at) = 6 AND delivered_at IS NOT NULL
), per_rider AS (
SELECT rider_id, COUNT(*) AS deliveries, SUM(is_late) AS late_deliveries FROM counted GROUP BY rider_id
)
SELECT p.rider_id, r.name, p.deliveries, p.late_deliveries, ROUND(p.late_deliveries * 100 / p.deliveries, 1) AS late_pct
FROM per_rider p JOIN Rider r ON r.rider_id = p.rider_id
WHERE p.late_deliveries / p.deliveries > 0.2← Refund Due on Cancelled Food Orders · Peak-Hour Share of Orders by City →