Longest On-Time Delivery Streak of Each Rider — SQL Problem
Riders get a badge for long runs of on-time deliveries. A streak is a run of consecutive deliveries of one rider (in delivered_at order, delivery_id…
- Difficulty: Hard
- Topics: Window Functions
- Dialect: MySQL
- Problem: #166
Problem statement
Riders get a badge for long runs of on-time deliveries. A streak is a run of consecutive deliveries of one rider (in delivered_at order, delivery_id breaking a tie) that were all on time; a late delivery ends it.
Return rider_id and longest_streak (the length of the rider's longest on-time streak, 0 if none of their deliveries was on time) for every rider who has at least one delivery. Order the rows by longest_streak descending, then rider_id ascending.
Tables
Table: Delivery
| Column | Type |
|---|---|
| delivery_id | int |
| rider_id | int |
| delivered_at | datetime |
| on_time | bool |
Primary key: delivery_id.
One row per completed delivery; on_time is 1 when it reached the customer within the promised time, else 0.
Examples
Example 1
Delivery
| delivery_id | rider_id | delivered_at | on_time |
|---|---|---|---|
| 1 | 7 | 2024-03-01 12:30:00 | 1 |
| 2 | 7 | 2024-03-01 13:10:00 | 1 |
| 3 | 7 | 2024-03-01 14:00:00 | 0 |
| 4 | 7 | 2024-03-01 19:20:00 | 1 |
| 5 | 7 | 2024-03-01 20:05:00 | 1 |
| 6 | 7 | 2024-03-01 20:40:00 | 1 |
| 7 | 8 | 2024-03-01 12:00:00 | 0 |
| 8 | 8 | 2024-03-01 13:00:00 | 0 |
| 9 | 9 | 2024-03-01 21:00:00 | 1 |
| 10 | 9 | 2024-03-01 21:00:00 | 0 |
| 11 | 9 | 2024-03-01 21:45:00 | 1 |
| 12 | 9 | 2024-03-01 21:50:00 | 1 |
Output
| rider_id | longest_streak |
|---|---|
| 7 | 3 |
| 9 | 2 |
| 8 | 0 |
How to solve Longest On-Time Delivery Streak of Each Rider
This is a gaps-and-islands problem: within each rider's ordered deliveries, find maximal runs of on-time rows. The trick is to give every row of one run the same key. Number each rider's deliveries in time order (rn), and separately number them within (rider, on_time) (rn_same). Along a run of on-time rows both numbers go up by one per row, so rn − rn_same stays constant; a late delivery in between bumps rn but not the on-time count, so the next run gets a new key. Group the on-time rows by that key, count them, and take each rider's maximum.
A second way to key the runs: a running count of late deliveries, SUM(CASE WHEN on_time = 0 …) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Every on-time row after the k-th late one carries k; summing on_time per key gives each run's length, and a rider with only late deliveries gets runs of length 0 — the 0 the statement asks for, with no extra join. The first query gets it from a LEFT JOIN of all riders and COALESCE.
Order matters twice: by delivered_at, not by id, and with delivery_id breaking equal timestamps so the run boundaries are fixed (rider 9's two 21:00 deliveries). The ROWS frame keeps the running sum per row even when timestamps repeat. Each plan is a sort per rider.
Reference solution (MySQL)
WITH seq AS (
SELECT rider_id, on_time,
ROW_NUMBER() OVER (PARTITION BY rider_id ORDER BY delivered_at, delivery_id) AS rn,
ROW_NUMBER() OVER (PARTITION BY rider_id, on_time ORDER BY delivered_at, delivery_id) AS rn_same
FROM Delivery
), runs AS (
SELECT rider_id, rn - rn_same AS island, COUNT(*) AS len
FROM seq
WHERE on_time = 1
GROUP BY rider_id, rn - rn_same
)
SELECT d.rider_id, COALESCE(MAX(r.len), 0) AS longest_streak
FROM (SELECT DISTINCT rider_id FROM Delivery) d
LEFT JOIN runs r ON r.rider_id = d.rider_id
GROUP BY d.rider_id
ORDER BY longest_streak DESC, d.rider_idAnother way
WITH marked AS (
SELECT rider_id, on_time,
SUM(CASE WHEN on_time = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY rider_id ORDER BY delivered_at, delivery_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS breaks
FROM Delivery
), runs AS (
SELECT rider_id, breaks, SUM(on_time) AS len FROM marked GROUP BY rider_id, breaks
)
SELECT rider_id, MAX(len) AS longest_streak FROM runs GROUP BY rider_id
ORDER BY longest_streak DESC, rider_id← Customers Whose Second Order Came Within a Week · Next-Month Retention of First-Order Cohorts →