Days off between shifts

driver_days has one row per day a driver was online. A missing day is a day off.

Ops wants to see every break: the last day a driver was online before it, the day they came back, and how many days they were off in between.

Return one row per break with:

column	type	meaning
driver_id	integer	the driver
last_online	date	the online day just before the break
back_online	date	the first online day after the break
days_off	integer	days between the two, not counting either end

Online on the 3rd and back on the 8th is a break of 4 days off (the 4th to the 7th).

Edge cases:

• Two online days in a row are not a break. Leave them out.
• Days off before a driver’s first online day or after their last don’t count. There’s nothing on both sides.
• The same day can appear twice for a driver. Treat it as one online day.

Row order doesn’t matter.

Tables: driver_days.

Approach

Sort each driver’s online days, look one row back, and keep the pairs that aren’t adjacent.

WITH ordered AS (
  SELECT driver_id,
         LAG(active_date) OVER (PARTITION BY driver_id ORDER BY active_date) AS last_online,
         active_date AS back_online
  FROM driver_days
)
SELECT driver_id,
       last_online,
       back_online,
       back_online - last_online - 1 AS days_off
FROM ordered
WHERE back_online - last_online > 1;

Why it works

After sorting, every gap in the calendar shows up as a jump between two neighbouring rows. LAG brings the neighbour onto the current row, so the jump is one subtraction.

The filter does three jobs at once. Consecutive days give a difference of 1 and drop out. A repeated day gives 0 and drops out. A driver’s first row has no previous day, so LAG returns NULL, the comparison is NULL, and WHERE drops it too. That’s why days off before the first shift never appear.

The filter has to sit outside the CTE. Window functions run after WHERE, so you can’t filter on LAG(...) in the same SELECT that computes it.

Under the hood

DuckDB hash-partitions the rows by driver_id, sorts each partition by active_date, and then walks each partition once. LAG with no frame clause is just “the value one row up”, so the walk is linear after the sort. The sort is the expensive part: roughly n log n over 578 rows here.