Longest On-Time Streak
Problem
A courier company logs each shift in `deliveries` with columns `(courier_id, shift_date, status)`. The pair `(courier_id, shift_date)` is unique, and `status` is one of 'OnTime', 'Late', or 'Missed'.
An on-time streak is a run of consecutive shift dates (in date order) for a courier where every shift is 'OnTime', with no 'Late' or 'Missed' shift breaking the run. For each courier report `courier_id` and `best_streak`: the length of their longest on-time streak. A courier who was never on time reports 0. Row order does not matter.
Tables
Example rows — the live problem includes the full dataset.
| courier_id | shift_date | status |
|---|---|---|
| 1 | 2024-03-01 | OnTime |
| 1 | 2024-03-02 | OnTime |
| 1 | 2024-03-05 | OnTime |
Expected output
Your answer should return 3 rows with the columns courier_id, best_streak.
Starter code (SQL)
SELECT *
FROM deliveries;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock