Solving the Classic SQL "Gaps and Islands" Problem: 3 Modern Approaches
If you've ever needed to find consecutive streaks in data — days a user logged in back to back, uninterrupted stretches of sensor readings, runs of matching status codes — you've run into the "gaps and islands" problem. The "islands" are the consecutive runs. The "gaps" are the breaks between them. SQL doesn't have a built-in FIND_STREAKS() function, so you build it with window functions instead.…
Finding consecutive streaks, or "islands," in data requires solving the "gaps and islands" problem. SQL lacks a built-in FIND_STREAKS() function, so developers must construct this functionality using window functions. The source material presents three modern approaches to tackle this problem.
Approach 1, the row-number trick, involves subtracting a row number from the date to create a group ID. By ordering login dates and applying ROW_NUMBER() to each user partition, a unique value is generated for each streak. When consecutive dates exist, the subtraction results in a consistent value, indicating the same streak. Gaps in dates lead to different values, effectively separating different islands. This method relies on evenly-spaced values and may not work accurately with irregular intervals.
Approach 2, flag-then-sum, offers a more flexible alternative. It begins by comparing each date to its preceding date using LAG(), flagging rows with a gap as 1 and consecutive dates as 0. A cumulative SUM() over this flag generates a unique identifier for each island. This approach provides more visibility into the process and allows for easy customization of the gap condition, making it a preferred choice among developers.
Approach 3, matching boundaries directly, eliminates the need for grouping and aggregation. It identifies streak start and end points by comparing each date to its neighbors (using LAG() and LEAD()). If a date lacks a preceding date (indicating a streak start) or a following date (indicating a streak end), it is flagged accordingly. By aligning these start and end points, the query directly retrieves the streak's start and end dates, thus identifying each island without the need for additional aggregation steps.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
