Q41: How do you solve the classic 'Gaps and Islands' problem in SQL?
The Gaps and Islands problem involves identifying continuous sequences of data (islands) and missing values between sequences (gaps) in ordered datasets.
Calculate island groupings by subtracting a row number sequence (ROW_NUMBER()) from the ordering column.
Islands Grouping Solution
WITH NumberedLogs AS (
SELECT
user_id,
log_date,
log_date - CAST(ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_date) AS INT) AS group_factor
FROM user_activity_logs
)
SELECT
user_id,
MIN(log_date) AS island_start,
MAX(log_date) AS island_end,
COUNT(*) AS consecutive_days
FROM NumberedLogs
GROUP BY user_id, group_factor;💡 Subtracting a sequential row number from a contiguous sequence yields a constant value (group_factor), grouping adjacent dates into islands.
Key Takeaways
- Subtracting sequential row numbers from sequence values creates constant identifiers for contiguous data ranges.
- A standard pattern for analyzing consecutive activity logs and date ranges.