← 🗄️ Data & SQL Engineering
Advanced
Gaps and Islands
Gaps and islands is the SQL pattern for collapsing a sequence of rows into the contiguous runs (islands) and the breaks between them (gaps). The trick is a difference of two row numbers that stays constant inside a run, giving every row in the same island an identical group key you can then aggregate. It powers sessionization, login streaks, and contiguous date-range queries, and interviewers love it because the naive self-join answer is both slow and wrong on ties.
Unlock the full curriculum — ₹2,000 / $25every concept + every answer · 6 months · no auto-renew
RELATED CONCEPTS
LESSONS THAT TEACH THIS
Foundations of Forward Deployed EngineeringResolve identities without hiding uncertain or unmatched records30 min→Foundations of Forward Deployed EngineeringDeclare the grain and prove that the totals survive joins30 min→The FDE EngagementMake replay, quarantine and reconciliation part of the pipeline contract30 min→
PRACTICE THIS IN REAL QUESTIONS
SQL & Data EngineeringWhat's the difference between RANK, DENSE_RANK and ROW_NUMBER, and when does the choice actually matter?→SQL & Data EngineeringFind the second-highest (or Nth-highest) salary per department.→SQL & Data EngineeringWrite SQL for the top 3 products by revenue in each region, per month.→SQL & Data EngineeringA table has duplicate rows for the same business key. Write SQL to keep only the latest version of each.→SQL & Data EngineeringWhy did my LEFT JOIN start behaving like an INNER JOIN after I added a WHERE filter?→SQL & Data EngineeringFind users who logged in on 3 or more consecutive days (gaps-and-islands).→