Each Driver's Longest Streak of Active Days

Hard
316.

Each Driver's Longest Streak of Active Days

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

ColumnTypeValues
trip_idint
driver_idint
trip_datedate
statusenumcompleted, cancelled

Primary key: trip_id.

A driver can have many trips on one day.

Example

The rows must come back in the order shown.

DriverTrip

trip_iddriver_idtrip_datestatus
112024-02-27completed
212024-02-28completed
312024-02-28completed
412024-02-29completed
512024-03-01completed
612024-03-03completed
722024-03-01completed
822024-03-02cancelled
922024-03-03completed
1022024-03-04completed
3 more rows

Output

driver_idstreak_startstreak_endstreak_days
12024-02-272024-03-014
22024-03-032024-03-042
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.

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

ColumnType
trip_idint
driver_idint
trip_datedate
statusenum(completed, cancelled)

Primary key: trip_id.

A driver can have many trips on one day.

Examples

Example 1

DriverTrip

trip_iddriver_idtrip_datestatus
112024-02-27completed
212024-02-28completed
312024-02-28completed
412024-02-29completed
512024-03-01completed
612024-03-03completed
722024-03-01completed
822024-03-02cancelled
922024-03-03completed
1022024-03-04completed
1122024-03-06completed
1222024-03-07completed
1332024-03-05cancelled

Output

driver_idstreak_startstreak_endstreak_days
12024-02-272024-03-014
22024-03-032024-03-042

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_id

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