Recognizing a Gaps-and-Islands Problem

When a senior interviewer asks you to find consecutive runs of something, you are looking at a gaps-and-islands problem. The name comes from a mental picture: rows that belong together form an island, and the breaks between them are gaps.

• An island is a maximal run of rows that are adjacent by some rule (consecutive integers, consecutive dates, or the same status repeated).
• A gap is the missing space between two islands.

Recognizing this class instantly is itself a senior signal. Many candidates reach for a tangle of self-joins; the elegant answer is almost always window functions.

The challenge is that interviewers rarely say "gaps and islands." They disguise it. Train your ear for phrasing like:

• "Find each period a user was continuously subscribed."
• "How many consecutive days did the server stay up?"
• "Which ranges of IDs are missing from this table?"
• "Collapse adjacent rows with the same status into one row."

Every one of these is the same shape: group rows that are next to each other, then report the start, end, or absence of those groups. Once you map the words to islands, the SQL writes itself.