Gap and island problems test pattern recognition.
The pattern: Find consecutive sequences or gaps in data.
Technique:
WITH numbered AS (
SELECT date, value,
ROW_NUMBER() OVER (ORDER BY date) as rn
FROM data
)
SELECT date, value,
date - rn * INTERVAL '1 day' as grp
FROM numbered
Consecutive dates minus row numbers produce the same group identifier.
Use cases: Session detection, streak counting, finding missing dates.