SQL Interview Prep · Lesson

Islands With Date and Status Changes

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

Islands With Date and Status Changes is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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');

Flag Where The Status Changes

Use LAG to compare each row's status to the previous one. When they differ (or the previous is NULL for the first row), a new island starts. We emit a 1 for a change and 0 otherwise.

Order strictly by date within the user. For our data the change flags are 1,0,1,1,0, marking the three period boundaries.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Running Sum Into A Period Key

As before, a running sum of the change flags yields a group key constant within each status period: 1,1,2,3,3 for our rows. Each distinct key is one continuous period.

The row-number-difference trick will not work here because status is not a number that steps by 1; the LAG-plus-running-sum recipe is the right tool when adjacency means 'unchanged value'.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Collapsing To Status Periods

Now GROUP BY user_id, status, and the running-sum key to report each period's span. Including status in the GROUP BY is safe because it is constant within a period, and it lets you select it without an aggregate.

The result is exactly three rows: active 01-01 to 02-01, paused 03-01 to 03-01, active 04-01 to 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

From Events To Half-Open Intervals

A subtle interview point: an event date marks when a status started, and the period truly ends when the next status begins, not on the last same-status event date. The correct period end is often the next period's start, modeled as a half-open interval [start, next_start).

Compute the next period's start with LEAD over the collapsed periods, leaving the final period open-ended (NULL or 'current').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Handling Repeated Status In A Row

What if the log has redundant rows like active, active, active with no change between them? The change flag is 0 for the repeats, so the running sum keeps them in one island automatically. That is the desired behavior: consecutive identical statuses collapse into a single period.

This natural deduplication of repeats is a key advantage of the change-flag method and is worth calling out to the interviewer.

When Gaps In Time Should Break A Period

Sometimes 'same status' is not enough; a large time gap should also break the period even if the status is identical. For example, active in January then active again after a six-month silence might count as two periods.

Extend the change flag with a second condition: start a new island when the status changes or the time since the previous event exceeds a threshold. This composes both adjacency rules cleanly.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Counting Distinct State Switches

A natural follow-up: "How many times did this user switch status?" That is simply the count of change flags minus the very first one (which marks the initial state, not a switch).

Equivalently, the number of periods minus 1. The running-sum key already encodes this, so the answer falls out of the same machinery you built for the periods.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Why This Beats Self-Joins Here

A self-join solution to status periods would need to pair each row with its neighbor, detect changes, then stitch boundaries together, an error-prone multi-step ordeal that struggles with three or more periods.

The LAG-flag-runningsum-groupby pipeline handles any number of periods in a single pass with no joins. Articulating this contrast, linear single-pass versus quadratic self-join, is exactly the senior reasoning interviewers reward.

A Reusable Template

Memorize this four-clause template; it solves the entire status-island family by changing only the adjacency test in the CASE:

flag: CASE with LAG to detect a new island.
key: running SUM of the flag, partitioned and ordered.
collapse: GROUP BY the partition column, the status, and the key.
interval (optional): LEAD for half-open period ends.

Same skeleton powers consecutive integers, dates, and statuses; only the CASE condition changes.

Quick Check

Confirm you grasp the status-island grouping rule.

Recap: Status and Date Islands

You can now solve the richest gaps-and-islands variant:

Adjacency = status unchanged from the previous row; flag changes with LAG.
Running-sum the change flags into a per-period group key.
Collapse with GROUP BY user_id, status, key to get period spans.
Use LEAD for half-open interval ends; extend the flag to break on large time gaps.
Repeated identical rows collapse automatically; switch counts fall out of the same flags.
One reusable template covers integers, dates, and statuses, only the CASE changes.

That completes the gaps-and-islands course, a reliable senior-level signal in SQL interviews.

Frequently asked questions

Is the “Islands With Date and Status Changes” lesson free?

Yes — the full text of “Islands With Date and Status Changes” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Islands With Date and Status Changes”?

Grouping consecutive same-status periods, a common subscription-state question. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Islands With Date and Status Changes” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep