Longest run of consecutive days

Constraints

1. 1Days, not deliveries. Three deliveries on one day is one day, so deduplicate before you count anything.
2. 2A streak is consecutive calendar days with no gap. A courier who worked five separate days with a gap between each has a longest streak of 1, not 5.
3. 3Every courier must appear, including the ones whose longest streak is 1 — the point of the report is the contrast.
4. 4Build the runs first, then take the maximum over them. Trying to find the longest streak in a single pass without identifying the runs is where this question goes wrong.
5. 5Order by longest_streak descending, then courier ascending.

Worked example

Vikram delivered on 2026-03-01, 02, 03 and 04 — four consecutive days and no gap — so his longest streak is 4, the highest in the dataset. Asha delivered on 03-01, 03-02 and then 03-05. That is a run of two and a run of one, so her longest streak is 2 even though she worked on three separate days. The distinction between 'days worked' and 'longest streak' is the whole question. Asha and Nita both worked three days; their streaks differ from Vikram's because of where the gaps fall, and a plain COUNT(DISTINCT delivered_on) cannot see that.

What this tests

Whether you can aggregate over the islands rather than over the rows — the second half of gaps and islands, and the half interviews actually ask for.