Longest online streak per driver

driver_days has one row per day a driver was online. A run of consecutive online days is a streak. Days off end a streak.

Ops wants each driver’s longest streak and the day it started.

Return one row per driver in driver_days with:

column	type	meaning
driver_id	integer	the driver
longest_streak_days	integer	number of days in the driver’s longest streak
streak_start	date	first day of that streak

Edge cases:

• If a driver has two streaks of the same longest length, report the one that started first.
• A driver with a single online day has a longest streak of 1.
• The same day can appear twice for a driver. Count it once.

Row order doesn’t matter.

Tables: driver_days.

Approach

Label each streak with date - row_number, group by the label, then pick the longest streak per driver.

WITH days AS (
  SELECT DISTINCT driver_id, active_date
  FROM driver_days
),
islands AS (
  SELECT driver_id,
         active_date,
         active_date - CAST(ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY active_date) AS INTEGER) AS island
  FROM days
),
streaks AS (
  SELECT driver_id,
         MIN(active_date) AS streak_start,
         COUNT(*)         AS streak_days
  FROM islands
  GROUP BY driver_id, island
)
SELECT driver_id,
       streak_days AS longest_streak_days,
       streak_start
FROM streaks
QUALIFY ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY streak_days DESC, streak_start) = 1;

Why it works

Take online days 3, 4, 5, 9, 10. Their row numbers are 1, 2, 3, 4, 5. Subtract and you get 2, 2, 2, 5, 5. Every streak collapses to one value, and a gap changes the value. The label itself is a meaningless date. It only has to be equal inside a streak and different across streaks.

The DISTINCT matters. With day 4 logged twice, the row numbers become 1, 2, 3, 4 for days 3, 4, 4, 5, and the labels 2, 2, 1, 1 split one streak into two.

The tie-breaker streak_start in the final ORDER BY makes the answer deterministic when two streaks have the same length.

Under the hood

There are two window passes and one hash aggregate. The first window sorts each driver’s days to hand out row numbers. The GROUP BY builds a hash table keyed by (driver_id, island). The QUALIFY window sorts each driver’s streaks by length and keeps row 1. QUALIFY is a DuckDB (and Snowflake, BigQuery) shortcut for wrapping the query and filtering WHERE rn = 1 outside it.