Gaps and islands

You meet it when#

"How many consecutive days did this device report?" "What was the longest streak?" "Which windows was the monitor offline?" Anything where the answer is about runs rather than rows.

The trick#

For a sequence of consecutive integers, subtracting a row number leaves a constant. Break the sequence and the constant changes.

text

date        rn   date − rn
2026-03-01   1   2026-02-28
2026-03-02   2   2026-02-28   ← same island
2026-03-03   3   2026-02-28
2026-03-07   4   2026-03-03   ← gap: new island
2026-03-08   5   2026-03-03

That derived column is a group key. Everything else is a normal GROUP BY.

The pattern#

sql

with numbered as (
    select device_id,
           reading_date,
           reading_date - (row_number() over (partition by device_id
                                              order by reading_date))::int as island
    from daily_readings
)
select device_id,
       min(reading_date) as run_start,
       max(reading_date) as run_end,
       count(*)          as days
from numbered
group by device_id, island
having count(*) > 1
order by days desc;

Finding the gaps instead#

The complement — the windows where nothing arrived — is a lag and a filter:

sql

select device_id,
       lag(reading_date) over (partition by device_id order by reading_date) as gap_start,
       reading_date                                                          as gap_end,
       reading_date - lag(reading_date) over (partition by device_id
                                              order by reading_date)         as gap_days
from daily_readings
qualify gap_days > 1;