Three SQL Window Function Techniques to Solve the Gaps and Islands Problem
The 'gaps and islands' problem in SQL involves identifying consecutive streaks in data, such as uninterrupted user login days, and the breaks between them. Since SQL lacks a native streak-detection function, developers rely on combinations of window functions like ROW_NUMBER(), LAG(), and running SUM() to build solutions. The classic approach subtracts a sequential row number from each date, producing a shared computed value for rows within the same streak that shifts whenever a gap appears. A second, more readable method uses LAG() to flag rows that begin a new streak, then applies a cumulative SUM() to assign each streak a unique group ID. Both techniques, along with a third variation, ultimately produce the same output — the start date, end date, and length of each consecutive streak per user.
