SQL Interview Prep · Lesson

Islands With Date and Status Changes

Grouping consecutive same-status periods, a common subscription-state question.

Islands Defined By A Changing Value

The most business-relevant gaps-and-islands variant groups consecutive rows that share the same status, collapsing a noisy event log into clean state periods. Classic prompt: "Given a subscription event log, return one row per continuous period the user stayed in each status."

Here adjacency does not mean 'values differ by 1'. It means the status is unchanged from the previous row. A new island begins the moment the status flips. This is where the LAG-based technique shines over the pure row-number trick.

The Subscription Sample

Consider a sub_events table for one user, ordered by date:

2026-01-01 active
2026-02-01 active
2026-03-01 paused
2026-04-01 active
2026-05-01 active

The desired output is three status periods: active Jan-Feb, paused Mar, active Apr-May. Note that the two active stretches are separate islands because a paused period interrupts them. Same status, but not consecutive, means different islands.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

All lessons in this course

← Back to SQL Interview Prep