How do you find consecutive days (gaps and islands)?
Model answer
A classic trick: subtract ROW_NUMBER() OVER (ORDER BY date) days from each date. Consecutive dates give the same result, so grouping by it identifies each streak. Then use MIN, MAX and COUNT per group.
Learn this topic
Window Functions: ROW_NUMBER, RANK and Running Totals in the course SQL for Beginners: Learn Databases Step by Step.
Sign in to add your own answer.