MODULE 6 OF 8: WINDOW FUNCTIONS • LESSON 5 OF 5~22 min read

The Gaps and Islands Problem: Consecutive Streaks and Groups

The Gaps and Islands problem is the defining advanced SQL interview test. Learn how to group consecutive rows (islands) and detect missing intervals (gaps) using the row number difference and lag techniques.

•

Previous Next Lesson

Key Takeaways at a Glance:

•The Gaps and Islands problem classifies contiguous sequences (islands) and missing intervals (gaps).
•The ROW_NUMBER difference trick: subtracting a continuous ROW_NUMBER() from a sequence value (date - rn) produces a constant group identifier for contiguous streaks.
•Classic interview questions: consecutive user login streaks, contiguous hotel booking dates, active sensor uptime periods.
•The Flag / Cumulative Sum approach marks island transitions with a conditional flag and applies a running SUM to form group IDs.

Foundational Principles

Core Concept Breakdown

Essential database invariants, relational theory, and execution behavior.

1What are Islands and Gaps?

In relational data, consecutive sequential events (such as user logins on back-to-back calendar days or sequential ticket numbers) form continuous sequences called 'Islands'. The missing intervals or breaks between these sequences are called 'Gaps'. A classic interview question is: 'Find all users who logged in for at least 3 consecutive days' or 'Find available meeting room time slots that have no bookings'.

Free Preview Limit Reached (2/2 Lessons Used)

Sign In to Unlock Full Lesson Content

Visitors are limited to 2 free preview lessons. Create a free StudyVex account to unlock all 38 comprehensive theory chapters, runnable query walkthroughs, visual input & result tables, and interactive placement quizzes.