Runs of consecutive delivery days

Constraints

1. 1A run is a set of consecutive calendar days. A single day with gaps either side is a run of length 1 and must still appear.
2. 2Deduplicate the days first. A courier who made three deliveries on one day worked one day, and counting rows instead of days makes every run too long.
3. 3Do not hard-code the date range. The technique has to work for a courier who worked every day and for one who worked twice with a gap.
4. 4The intended approach is the date-minus-row-number trick: within a courier, subtract the row number from the date. Consecutive days shift onto the same constant, which becomes the group key.
5. 5Order by courier, then run_start.

Worked example

Kabir delivered on 2026-03-01, then 2026-03-03, 2026-03-04 and 2026-03-05. Numbered in order those are rows 1, 2, 3, 4. Subtract the row number as days: 03-01 minus 1 day is 02-28; 03-03 minus 2 is 03-01; 03-04 minus 3 is 03-01; 03-05 minus 4 is 03-01. The three consecutive days collapse onto the same key and the isolated day sits on its own — so Kabir has two runs, one of length 1 and one of length 3. That is the whole trick: within a run, the date and the row number increase in lockstep, so their difference is constant. A gap breaks the lockstep and starts a new constant.

What this tests

Gaps and islands — the pattern behind streaks, sessions and uptime windows — and whether you deduplicated the days before numbering them.