Riders Missing the Delivery SLA in June 2024

Medium
156.

Riders Missing the Delivery SLA in June 2024

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

ColumnType
rider_idint
namevarchar
cityvarchar

Primary key: rider_id.

One row per delivery partner.

Table: Delivery

ColumnType
delivery_idint
rider_idint
picked_up_atdatetime
delivered_atdatetime
promised_minutesint

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Rider

rider_idnamecity
1ArjunBengaluru
2PoojaBengaluru
3KabirBengaluru

Delivery

delivery_idrider_idpicked_up_atdelivered_atpromised_minutes
112024-06-03 12:10:002024-06-03 12:40:0025
212024-06-03 19:00:002024-06-03 19:20:0020
312024-06-04 13:00:002024-06-04 13:18:0020
422024-06-05 20:00:002024-06-05 20:22:0020
522024-06-05 21:00:002024-06-05 21:15:0020
622024-06-06 12:00:002024-06-06 12:19:0020
722024-06-06 13:00:002024-06-06 13:20:0020
822024-06-07 19:30:002024-06-07 19:45:0020
932024-05-31 22:00:002024-05-31 22:50:0030
1032024-06-08 20:00:00NULL30
1 more row

Output

rider_idnamedeliverieslate_deliverieslate_pct
1Arjun3133.3
3Kabir11100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
rider_idint
namevarchar
cityvarchar

Primary key: rider_id.

One row per delivery partner.

Table: Delivery

ColumnType
delivery_idint
rider_idint
picked_up_atdatetime
delivered_atdatetime
promised_minutesint

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_idnamecity
1ArjunBengaluru
2PoojaBengaluru
3KabirBengaluru

Delivery

delivery_idrider_idpicked_up_atdelivered_atpromised_minutes
112024-06-03 12:10:002024-06-03 12:40:0025
212024-06-03 19:00:002024-06-03 19:20:0020
312024-06-04 13:00:002024-06-04 13:18:0020
422024-06-05 20:00:002024-06-05 20:22:0020
522024-06-05 21:00:002024-06-05 21:15:0020
622024-06-06 12:00:002024-06-06 12:19:0020
722024-06-06 13:00:002024-06-06 13:20:0020
822024-06-07 19:30:002024-06-07 19:45:0020
932024-05-31 22:00:002024-05-31 22:50:0030
1032024-06-08 20:00:00NULL30
1132024-06-09 20:00:002024-06-09 20:31:0030

Output

rider_idnamedeliverieslate_deliverieslate_pct
1Arjun3133.3
3Kabir11100

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 →