Each Driver's Longest Streak of Active Days — SQL Problem
A driver is active on a day when they completed at least one trip that day; cancelled trips do not make a day active.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #316
Problem statement
A driver is active on a day when they completed at least one trip that day; cancelled trips do not make a day active. The loyalty programme rewards long runs of consecutive active days.
For every driver with at least one active day, return driver_id, streak_start, streak_end and streak_days of their longest run of consecutive active days. If a driver has several runs of the same longest length, report the earliest one. Order by driver_id.
Tables
Table: DriverTrip
| Column | Type |
|---|---|
| trip_id | int |
| driver_id | int |
| trip_date | date |
| status | enum(completed, cancelled) |
Primary key: trip_id.
A driver can have many trips on one day.
Examples
Example 1
DriverTrip
| trip_id | driver_id | trip_date | status |
|---|---|---|---|
| 1 | 1 | 2024-02-27 | completed |
| 2 | 1 | 2024-02-28 | completed |
| 3 | 1 | 2024-02-28 | completed |
| 4 | 1 | 2024-02-29 | completed |
| 5 | 1 | 2024-03-01 | completed |
| 6 | 1 | 2024-03-03 | completed |
| 7 | 2 | 2024-03-01 | completed |
| 8 | 2 | 2024-03-02 | cancelled |
| 9 | 2 | 2024-03-03 | completed |
| 10 | 2 | 2024-03-04 | completed |
| 11 | 2 | 2024-03-06 | completed |
| 12 | 2 | 2024-03-07 | completed |
| 13 | 3 | 2024-03-05 | cancelled |
Output
| driver_id | streak_start | streak_end | streak_days |
|---|---|---|---|
| 1 | 2024-02-27 | 2024-03-01 | 4 |
| 2 | 2024-03-03 | 2024-03-04 | 2 |
How to solve Each Driver's Longest Streak of Active Days
This is gaps and islands. First collapse the data to distinct (driver, active day) pairs from completed trips: three trips on one day are one day, and a day with only cancellations is not active.
Number each driver's active days in date order with ROW_NUMBER(). Along a run of consecutive days both the date and the row number go up by one, so trip_date − row_number days stays constant; a skipped day bumps it. That constant is the island's key: group by (driver, key) for MIN, MAX and COUNT of each run. It works across month ends (28 Feb → 29 Feb → 1 Mar in 2024) because date arithmetic is calendar-aware.
Finally pick one run per driver: ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY streak_days DESC, streak_start) makes the earliest of equal-longest runs number 1. The alternative marks a row as a new run when the gap to the previous active day (via LAG) is not exactly one, numbers runs with a running SUM, and selects the best run with NOT EXISTS. Both are a couple of sorts per driver.
Reference solution (MySQL)
WITH active_days AS (
SELECT DISTINCT driver_id, trip_date
FROM DriverTrip
WHERE status = 'completed'
), islands AS (
SELECT driver_id, trip_date,
DATE_SUB(trip_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY trip_date) DAY) AS grp
FROM active_days
), streaks AS (
SELECT driver_id, MIN(trip_date) AS streak_start, MAX(trip_date) AS streak_end, COUNT(*) AS streak_days
FROM islands
GROUP BY driver_id, grp
), ranked AS (
SELECT streaks.*, ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY streak_days DESC, streak_start) AS rn
FROM streaks
)
SELECT driver_id, streak_start, streak_end, streak_days
FROM ranked
WHERE rn = 1
ORDER BY driver_idAnother way
WITH active_days AS (
SELECT driver_id, trip_date FROM DriverTrip WHERE status = 'completed' GROUP BY driver_id, trip_date
), flagged AS (
SELECT driver_id, trip_date,
CASE WHEN DATEDIFF(trip_date, LAG(trip_date) OVER (PARTITION BY driver_id ORDER BY trip_date)) = 1 THEN 0 ELSE 1 END AS is_start
FROM active_days
), numbered AS (
SELECT driver_id, trip_date,
SUM(is_start) OVER (PARTITION BY driver_id ORDER BY trip_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS streak_no
FROM flagged
), streaks AS (
SELECT driver_id, MIN(trip_date) AS streak_start, MAX(trip_date) AS streak_end, DATEDIFF(MAX(trip_date), MIN(trip_date)) + 1 AS streak_days
FROM numbered GROUP BY driver_id, streak_no
)
SELECT s.driver_id, s.streak_start, s.streak_end, s.streak_days FROM streaks s
WHERE NOT EXISTS (
SELECT 1 FROM streaks t WHERE t.driver_id = s.driver_id
AND (t.streak_days > s.streak_days OR (t.streak_days = s.streak_days AND t.streak_start < s.streak_start))
)
ORDER BY s.driver_id← Couriers Delivering More Than Their Hub's Average in June · Rider App Sessions and Booking Conversion →