Longest On-Time Delivery Streak of Each Rider

Hard
166.

Longest On-Time Delivery Streak of Each Rider

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

ColumnType
delivery_idint
rider_idint
delivered_atdatetime
on_timebool

Primary key: delivery_id.

One row per completed delivery; on_time is 1 when it reached the customer within the promised time, else 0.

Example

The rows must come back in the order shown.

Delivery

delivery_idrider_iddelivered_aton_time
172024-03-01 12:30:001
272024-03-01 13:10:001
372024-03-01 14:00:000
472024-03-01 19:20:001
572024-03-01 20:05:001
672024-03-01 20:40:001
782024-03-01 12:00:000
882024-03-01 13:00:000
992024-03-01 21:00:001
1092024-03-01 21:00:000
2 more rows

Output

rider_idlongest_streak
73
92
80
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.

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

ColumnType
delivery_idint
rider_idint
delivered_atdatetime
on_timebool

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_idrider_iddelivered_aton_time
172024-03-01 12:30:001
272024-03-01 13:10:001
372024-03-01 14:00:000
472024-03-01 19:20:001
572024-03-01 20:05:001
672024-03-01 20:40:001
782024-03-01 12:00:000
882024-03-01 13:00:000
992024-03-01 21:00:001
1092024-03-01 21:00:000
1192024-03-01 21:45:001
1292024-03-01 21:50:001

Output

rider_idlongest_streak
73
92
80

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_id

Another 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 →