Skip to main content
CodeSparring

Gaps and Islands: Consecutive Streaks and Run Detection

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard42 minproduct-analyticsROW_NUMBERgaps-and-islandsstreak detectiondate arithmeticGROUP BY on a derived key

The most-failed pattern on the live SQL screen: find consecutive runs and the breaks between them with the row-number-difference template.

The most-failed pattern on the SQL screen

Gaps and islands is repeatedly named the single most-failed pattern on a live Data Engineering SQL screen. The task always has the same shape: find the consecutive runs (the "islands") in a sequence and the breaks (the "gaps") between them. Longest login streak, consecutive days a metric held, unbroken spans where a price never moved, back-to-back on-call shifts: all one technique. Learn this one template and you have handled a large slice of the analytics questions an interviewer can hand you.

The four-step template

Work on daily_logins(user_id, login_date). The whole trick is to build a key that stays constant across a run and changes the moment the run breaks, then group on it.

  1. Number each user's rows in date order. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) gives 1, 2, 3, ... down each user's logins.
  2. Subtract the row number from the date. Inside a run of consecutive days the date climbs by one each row and so does the row number, so date minus row_number lands on the same anchor date for every row in that run. A gap makes the date jump ahead of the row number, so the anchor shifts to a new value. In SQLite: date(login_date, '-' || rn || ' days').
  3. Group by user and anchor. Each (user_id, anchor) pair is exactly one island. MIN(login_date) is its start, MAX(login_date) its end, and COUNT(*) its length.
  4. Aggregate the islands. MAX(length) per user is the longest streak; a filter on the run start or length answers "which spans".

The demo below (turn on the input panel) materializes the anchor next to each date so you can watch it hold steady inside a run and jump right after the gap.

Table
date minus row_number lands on the same anchor for every row in a run, then shifts when the gap breaks it.
login_daternisland_anchor
2026-03-0112026-02-28
2026-03-0222026-02-28
2026-03-0332026-02-28
2026-03-0542026-03-01
2026-03-0652026-03-01
date minus row_number lands on the same anchor for every row in a run, then shifts when the gap breaks it.

The LAG variant

The same islands fall out of LAG: a new island starts wherever the previous row is not the day before. Flag a boundary with prev_date IS NULL OR prev_date < date(login_date, '-1 day'), then take a running SUM of that flag as the island id. Both forms are correct. The row-number-difference form is shorter to write under pressure; the LAG form reads more obviously to a reviewer.

Where this shows up

Streaks, consecutive purchases, unchanged-price spans, subscription-state runs, uptime windows: whenever the prompt says "consecutive", "in a row", "unbroken", or "back to back", reach for this template.

Interview nuance: the row-number-difference trick assumes one row per unit (one row per day here). If a user can appear twice on the same day, deduplicate to distinct dates first with SELECT DISTINCT user_id, login_date, or the doubled row numbers will fracture a real run into false gaps.

In the warehouse this differs. The shape is identical everywhere; only the date math changes. SQLite uses date(d, '+1 day') and julianday() differences, while Snowflake and BigQuery use DATEADD/DATE_ADD and DATEDIFF/DATE_DIFF. In Snowflake, BigQuery, and Databricks you can also wrap the final streak filter in a QUALIFY clause instead of a subquery; SQLite has no QUALIFY, so you filter in an outer query.

Sample data for this example
CREATE TABLE daily_logins (user_id INTEGER, login_date TEXT);
INSERT INTO daily_logins (user_id, login_date) VALUES
  (1, '2026-03-01'),
  (1, '2026-03-02'),
  (1, '2026-03-03'),   -- run of three
  (1, '2026-03-05'),   -- gap on 03-04 starts a new run
  (1, '2026-03-06');
Worked example (SQL)
-- The anchor stays constant inside a run and jumps after the gap.
SELECT user_id, login_date, rn,
       date(login_date, '-' || rn || ' days') AS island_anchor
FROM (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM daily_logins
)
ORDER BY user_id, login_date;

Apply

Your turn

The task this lesson builds to.

Write a query that returns each user's longest consecutive-day login streak as (user_id, streak_length), over daily_logins(user_id, login_date).

Use the row-number-difference template: number each user's logins in date order, subtract the row number from the date to get a per-run anchor, group by (user_id, anchor) to size each run, then keep the longest run per user. Alias the columns exactly user_id and streak_length.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that returns, for each product, the date ranges during which its price stayed unchanged, as (product_id, price, valid_from, valid_to), over price_history(product_id, price, effective_date).

Each row is one contiguous span at a single price. If a product returns to an earlier price after changing away and back, that is a separate span. Alias the columns exactly product_id, price, valid_from, valid_to.

3 hints and 1 automated check are waiting in the workspace.