Gaps and Islands
Last Updated: May 3, 2026
8 min read
Some questions sound simple but are surprisingly hard to express in SQL: "What's each user's longest daily listening streak?" or "Which dates had zero streams on the platform?"
These are gaps-and-islands problems. Islands are consecutive sequences in your data. Gaps are the breaks between them. Solving them requires a technique that turns ROW_NUMBER into a grouping tool, and once you see how it works, it becomes one of the most reusable patterns in your SQL toolkit.