IntermediateSQL · 25 min

Gaps and Islands: Intermediate

Past the basics, gaps and islands is no longer about whether you know the trick. The interviewer assumes you do. What they are testing now is whether you know the second canonical form (LAG plus cumulative SUM), when each form wins, and how to handle the variants that show up in real reporting data. Sessionization with a threshold gap. Conditional islands where only certain rows count. Per-island filtering against an aggregate. The query shape extends in three directions; the candidate who reaches for the right extension on first read has shipped this pattern in production.

Know both canonical forms (row-number difference and LAG plus cumulative SUM) and when each wins

Extend the trick to threshold gaps (sessionization) and conditional islands

Handle per-island filtering at the right point in the pipeline

Articulate the equivalence between the two forms; they share the same algebra

The ROW_NUMBER Difference Trick

Daily Life

Interviews

Recognize gaps-and-islands triggers: "consecutive days," "unbroken streaks," "contiguous ranges."

Here is the question that recurs in interviews past the basics. 'Compute user sessions from an events table, where a session is a sequence of events with no gap longer than thirty minutes.' This is gaps and islands with a threshold instead of strict adjacency. The row-number difference trick from the beginner lesson does not apply directly; the gap is not 'value plus one,' it is 'value within some delta.' The LAG-plus-cumulative-SUM form generalizes to this case. Knowing when to reach for it, and being able to explain why both forms produce the same answer, is the depth this question rewards.

What separates this from the beginner question:

▸Gap is a threshold (30 minutes), not strict adjacency
▸Row-number difference trick does not apply directly
▸LAG plus cumulative SUM is the generalization
▸The CASE expression encodes the boundary condition

The two canonical forms

Form 1 is the row-number difference trick. Value column minus row number is constant within a run because both advance in lockstep at every row. Best when 'consecutive' means strict adjacency: integers off by one, dates one day apart, hours one hour apart. Form 2 is LAG plus cumulative SUM. Use LAG to expose each row's predecessor; compute a boolean is_new_run that is 1 when the row starts a new run (predecessor is NULL or the gap exceeds the threshold) and 0 otherwise; cumulative SUM of the boolean produces a run identifier that increments at each boundary. Best when 'consecutive' has a threshold or condition. Both produce equivalent run identifiers; the algebra is the same but expressed differently.

•Reach for the row-number difference when

The sequence column has strict adjacency (off-by-one integers, off-by-one dates)
The runs are dense (no skipped values within a run)
Readability matters; the trick is one line of algebra
Performance matters; one window function vs three for the LAG form

✓Reach for LAG plus cumulative SUM when

The gap is a threshold (sessions within N minutes, runs within N days)
The boundary condition is more complex than "value plus one"
You need to add conditions per row (status changes, attribute flips)
The CASE in the boundary expression encodes business logic

What the interviewer is silently scoring

Three things, in order. First: do you recognize the variant on first read? Sessionization with a threshold is not the same shape as 'longest streak of consecutive days'; reaching for the wrong form costs ten minutes. Second: can you defend the choice between the two forms with a specific reason? 'LAG plus cumulative SUM because the gap is a threshold' is the answer. Third: can you articulate the equivalence? Saying 'both forms produce the same run identifier; the difference is what defines a boundary' is the move that proves you understand the algebra, not just the recipes.

When the interviewer hands you the variant, name it before writing SQL. 'This is gaps and islands with a threshold gap. I'll use the LAG-plus-cumulative-SUM form because the row-number difference does not generalize to threshold gaps.' Two sentences. The interviewer has heard the form choice with a reason; the rubric item for 'can the candidate pick the right tool' is now scored.

Date Gaps vs Integer Gaps

Daily Life

Interviews

Derive island group IDs by subtracting ROW_NUMBER from the sequence value to produce a constant per island.

The LAG-plus-cumulative-SUM form is three CTEs instead of two. LAG exposes the predecessor's value. A CASE expression flags rows that start a new run. A cumulative SUM of the flag produces a run identifier. Each CTE is one job; the query reads top-down as the story of the calculation.

The canonical sessionization query

	/* Sessionize user events: new session when the gap exceeds 30 minutes */
	WITH lagged AS (
	SELECT
	user_id,
	event_time,
	LAG(event_time, 1) OVER (
	PARTITION BY user_id
	ORDER BY event_time
	) AS prev_event_time
	FROM events
	),
	flagged AS (
	SELECT
	user_id,
	event_time,
	CASE
	WHEN prev_event_time IS NULL THEN 1 /* first event for this user */
	WHEN event_time - prev_event_time > INTERVAL '30 minutes' THEN 1 /* gap exceeded */
	ELSE 0
	END AS is_new_session
	FROM lagged
	),
	sessions AS (
	SELECT
	user_id,
	event_time,
	SUM(is_new_session) OVER (
	PARTITION BY user_id
	ORDER BY event_time
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS session_id
	FROM flagged
	)

	SELECT
	user_id,
	session_id,
	MIN(event_time) AS session_start,
	MAX(event_time) AS session_end,
	COUNT(*) AS event_count,
	MAX(event_time) - MIN(
	event_time
	) AS session_duration
	FROM sessions
	GROUP BY user_id, session_id
	ORDER BY user_id, session_start

Reading the query top-down

The lagged CTE adds a prev_event_time column via LAG; for the first event per user, prev_event_time is NULL. The flagged CTE adds a boolean is_new_session: 1 if there is no predecessor or if the gap from the predecessor exceeds thirty minutes, 0 otherwise. The sessions CTE adds a cumulative SUM of is_new_session, partitioned by user_id; this sum increments at every session boundary and stays constant within a session, producing a unique session_id per (user, session). The final SELECT groups by (user_id, session_id) and aggregates: start, end, event count, duration. Four CTEs, each named for what it produces.

Why three window functions, not one

Each CTE uses one window function: LAG in the first, then implicitly nothing in the flagged CTE (it is a CASE expression, not a window), then SUM OVER in the third. Modern engines run these as three passes over the same partition because all three windows share the same PARTITION BY user_id ORDER BY event_time definition. The optimizer recognizes the shared partition and sorts the data once, then applies all three window operators in sequence. The cost is closer to one partition shuffle, not three. State this when the interviewer asks about performance: 'the three windows share the same partition definition, so the engine sorts once and applies all three in one scan.'

Why the row-number difference does not work here

The row-number difference relies on the sequence column advancing by exactly one per row within a run. Event timestamps do not. Two events might be one minute apart; the next two might be twenty minutes apart; the run is still 'within thirty minutes,' but event_time minus rn is not constant across them. The trick depends on strict adjacency. Sessionization is not strict adjacency; it is threshold adjacency. The LAG-plus-cumulative-SUM form encodes the threshold in the CASE expression, which is why it generalizes.

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is mandatory on the cumulative SUM. Without it, the engine defaults to RANGE, which treats tied event_time values as a peer group and assigns them the same cumulative sum. If two events happen at the same nanosecond (rare but possible at scale), RANGE collapses them and the session_id becomes wrong. ROWS is the right default for cumulative sums in this context. The same rule from the running-totals lesson applies here.

TIP

When the interviewer asks why the SUM OVER works, point at the CASE expression and say: 'is_new_session is 1 at the start of each session and 0 within a session. Cumulative SUM increments by 1 at every boundary and adds 0 inside a session, so the running total is constant within a session and unique per session.' Two sentences. The proof is what scores; the SQL is the implementation.

Island Aggregates: Length, Start, End

Daily Life

Interviews

Adapt the technique for date sequences (DATE_DIFF minus ROW_NUMBER) vs integer sequences, handling weekends and holidays.

The other common variant is conditional islands. The runs are not defined by adjacency at all; they are defined by a status or attribute being constant for a stretch of rows. 'Find contiguous periods where a subscription was active.' 'Find stretches where a server was healthy.' 'Find runs of days the stock closed above its 50-day moving average.' These are gaps and islands where 'consecutive' means 'sharing the same value' rather than 'adjacent in the sequence.' The LAG form handles this case cleanly with one change to the boundary CASE.

The status-change variant

	/* Contiguous periods where a subscription was active */
	WITH lagged AS (
	SELECT
	subscription_id,
	snapshot_date,
	status,
	LAG(status, 1) OVER (
	PARTITION BY subscription_id
	ORDER BY snapshot_date
	) AS prev_status
	FROM subscription_snapshots
	),
	flagged AS (
	SELECT
	subscription_id,
	snapshot_date,
	status,
	CASE
	WHEN prev_status IS NULL
	OR prev_status != status THEN 1
	ELSE 0
	END AS is_new_run
	FROM lagged
	),
	runs AS (
	SELECT
	subscription_id,
	snapshot_date,
	status,
	SUM(is_new_run) OVER (
	PARTITION BY subscription_id
	ORDER BY snapshot_date
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) AS run_id
	FROM flagged
	)

	SELECT
	subscription_id,
	status,
	MIN(snapshot_date) AS run_start,
	MAX(snapshot_date) AS run_end,
	COUNT(*) AS run_length_days
	FROM runs
	WHERE status = 'active'
	GROUP BY subscription_id, status, run_id
	ORDER BY subscription_id, run_start

What changed in the boundary CASE

The boundary condition is no longer about a time gap; it is about a value change. is_new_run is 1 when the predecessor's status is different from the current row's status (or when there is no predecessor). The cumulative SUM then produces a run_id that increments every time the status flips. Within a contiguous active period, the status stays 'active' and is_new_run stays 0, so the run_id is constant. The moment the status flips to 'paused' or 'cancelled,' is_new_run becomes 1 and the run_id increments. Same algebra, different boundary.

Filtering at the right point

Notice the WHERE status = 'active' is in the final SELECT, after the runs CTE. This is critical. If you filter to status = 'active' before computing the run_id, you remove the non-active rows entirely, and the run_id no longer increments when the status was 'paused' between two 'active' stretches. The two separate active runs collapse into one. The filter has to be after the run_id is computed, not before. State this when the interviewer asks: 'I'm filtering after the run_id is computed, because filtering before would collapse adjacent active runs that had a paused stretch between them.'

•Filter before run_id (the bug)

Drops non-active rows from the source
The run_id no longer detects the boundary at status changes
Two separate active runs merge into one
Returns inflated run lengths and incorrect run counts

✓Filter after run_id (the fix)

Run_id correctly increments at every status change
Each contiguous active stretch gets its own run_id
Filter keeps only the active runs in the final output
Run lengths and run counts match the actual data

The general principle: when to filter

This is a recurring pattern in gaps-and-islands queries. If you filter the source before computing the run identifier, you remove the boundary information that defines the runs. The filter has to be applied at the point in the pipeline where it does not destroy the boundary signal. In sessionization, the filter is usually after the session_id (you might filter to sessions longer than N events). In status-change runs, the filter is after the run_id (you filter to the runs whose status matches the consumer's interest). The rule: compute the run identifier first, then filter.

The exception to this rule: if you filter the source by an entity (drop a single user, ignore a subscription tier), that filter is safe because it does not affect the boundary detection within any remaining entity. Filters that operate at the same grain as the run boundary are unsafe; filters that operate above the grain (per user, per entity) are safe. Articulating this distinction is what tells the interviewer you have thought about the filter placement, not just copied the pattern.

At Zoom in 2021, the SRE team built a 'longest contiguous healthy uptime per host' dashboard using a gaps-and-islands query against a snapshot table of host status checks. The first version filtered the source to status = 'healthy' before computing the run identifier, which silently merged adjacent healthy stretches that had unhealthy minutes between them. The dashboard reported uptime of 14 hours for a host that actually had a 5-minute outage in the middle; the SRE team noticed when an unrelated outage report referenced the same host with a 5-minute gap that the dashboard did not surface. The fix was to move the filter from the source CTE to the final SELECT, after the run_id was computed. The runbook line was 'filters on the boundary attribute go after the run identifier; filters on the entity go before.' That sentence has been the rubric for the SRE data engineering screen since.

Handling Ties and Duplicate Timestamps

Daily Life

Interviews

After identifying islands, compute streak length, start/end boundaries, and filter for streaks exceeding a threshold.

After the run_id is in place, the rest of the query is GROUP BY and aggregation. The interviewer at this level will probe whether you handle per-island thresholds and conditional aggregates correctly. The two most common probes: 'only return runs of at least N events' and 'compute statistics across the runs.' Each one tests where the threshold belongs in the query.

'Only return runs of at least N events'

This is a per-island threshold. The filter applies to the aggregate (COUNT, SUM, MAX) after the GROUP BY, which means it belongs in a HAVING clause. WHERE filters individual rows before grouping; HAVING filters groups after the aggregate has been computed.

	WITH sessions AS()

	SELECT
	user_id,
	session_id,
	MIN(event_time) AS session_start,
	MAX(event_time) AS session_end,
	COUNT(*) AS event_count
	FROM sessions
	GROUP BY user_id, session_id
	HAVING COUNT(*) >= 5
	ORDER BY user_id, session_start ;

HAVING COUNT(*) >= 5 filters out short sessions after the aggregate is computed. The query still computes the session_id correctly for every event; the filter only affects which sessions appear in the output. The interviewer may probe 'what if I want only the long sessions for a specific user?' The answer is to add WHERE user_id = X to the source CTE (before the LAG, so the run identifier is computed only for that user's events), or to the final SELECT (after the GROUP BY, to filter the output). Both are valid; pick based on whether other users' events should affect the boundary detection. For sessions per user, they should not, so the WHERE goes in the source CTE.

Computing statistics across the runs

The other common probe is 'show me the distribution of session lengths.' Once the per-session aggregate is computed, the next step is a query over the session-level result: average, percentiles, count of sessions per bucket. This requires nesting: the per-session query becomes a CTE, and the outer query computes the distribution over it.

	/* Distribution of session lengths per user */
	WITH per_session AS (
	/* ... the sessionization query as before ... */
	SELECT
	user_id,
	session_id,
	COUNT(*) AS event_count,
	MAX(event_time) - MIN(
	event_time
	) AS session_duration
	FROM sessions
	GROUP BY user_id, session_id
	)

	SELECT
	user_id,
	COUNT(*) AS total_sessions,
	AVG(event_count) AS avg_events_per_session,
	PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY event_count) AS median_events,
	PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY event_count) AS p90_events,
	AVG(session_duration) AS avg_duration
	FROM per_session
	GROUP BY user_id

Two-level aggregation: first per (user, session), then per user. The pattern generalizes: any time the consumer wants 'stats across the runs,' the query structure is the run-detection CTEs, then a per-run aggregate, then a per-entity aggregate over the per-run result. State the structure when the interviewer extends the question: 'this is a two-level aggregate; the inner level computes per-session stats, the outer level computes the distribution over those stats.'

The 'longest session' question, with ties

'Find the longest session per user' is the same shape as the longest-streak question from the beginner lesson, but at the session level. Two solutions: ORDER BY COUNT(*) DESC LIMIT 1 per user (requires window functions to handle the per-user partition), or a HAVING clause that compares against the per-user max. The window-function solution is cleaner. Pick RANK if ties should appear; pick ROW_NUMBER plus a tiebreaker if exactly one row per user is needed. The choice is the same Top-N-per-group decision applied to the aggregated session-level result.

	/* Longest session per user, with ties handled */
	WITH session_summary AS (
	/* ... sessionization plus per-session aggregate ... */
	SELECT
	user_id,
	session_id,
	COUNT(*) AS event_count
	FROM sessions
	GROUP BY user_id, session_id
	),
	ranked_sessions AS (
	SELECT
	session_summary.*,
	RANK() OVER (
	PARTITION BY user_id
	ORDER BY event_count DESC
	) AS r
	FROM session_summary
	)

	SELECT
	user_id,
	session_id,
	event_count
	FROM ranked_sessions
	WHERE r = 1

TIP

When the interviewer asks 'longest session per user,' immediately recognize it as Top-N-per-group applied to the session-level result. The two patterns compose: gaps and islands produces the session-level table; Top-N-per-group picks the longest per user. Naming the composition out loud is the move that proves you see how the patterns connect.

Grouping by the Computed Island Key

Daily Life

Interviews

Solve gaps-and-islands using LAG + cumulative SUM for interviewers who want the sessionization variant.

The closing escalation at this level covers three areas: performance on large tables, edge cases that ship to production, and the synthesis that ties the two canonical forms together. Hitting all three is what flips the verdict from hire to strong hire on this question.

Performance: partition shuffles dominate

Both canonical forms partition by the entity column (user_id, subscription_id) and order within the partition. The dominant cost on a distributed engine is the shuffle that co-locates each entity's rows on a single worker. The window functions themselves are cheap once the data is co-located. The optimization lever is to align the source table's clustering or partitioning with the query's partition key. If user_id is the partition column for the sessionization query, cluster the events table by user_id (on Snowflake/BigQuery) or partition by user_id (on Spark). The shuffle becomes a no-op and the query runs at the speed of a single-worker computation.

Edge case: the empty partition

If a user has no events, the source table has no rows for that user. The CTEs produce no rows for that user. The output has no rows for that user. This is correct behavior; the query implicitly handles empty entities. The interviewer may probe what happens when a user has exactly one event; the answer is that the user gets one session of length 1, because is_new_session is 1 for the first event (no predecessor) and the cumulative SUM is 1, and the GROUP BY produces one row. State this unprompted; it shows you have thought about the edge cases.

Edge case: same-timestamp events

Two events at the exact same event_time within a user's stream is the silent-bug case. The ORDER BY event_time inside LAG has a tie; the engine picks one row arbitrarily as the 'predecessor.' The LAG might return either event's timestamp, and the gap calculation might be zero (no gap) or might miss the actual boundary. The fix is a deterministic tiebreaker in the ORDER BY: ORDER BY event_time, event_id. This guarantees the same row is chosen as the predecessor every run of the query, which makes the result reproducible. Mention this when the interviewer asks about same-timestamp events: 'I would add event_id as a secondary ORDER BY for determinism on tied timestamps.'

ORDER BY event_time has a tieLAG picks predecessor arbitrarilyGap calculation might miss the actual boundarySame data → different session_ids across runsFix: ORDER BY event_time, event_id

•Forgetting the tiebreaker (the silent bug)

Two events at the same timestamp; engine picks predecessor arbitrarily
Same query against same data may return different session_ids run to run
Reproducibility issue; impossible to debug without seeing the underlying order
Tests pass in dev (no ties) but fail in prod (ties happen)

✓Adding a tiebreaker (the fix)

ORDER BY event_time, event_id guarantees a deterministic order
Same data produces the same result on every run
Tests reproduce in dev because the order is stable
One extra column in the ORDER BY; no other change needed

The synthesis: two forms, one algebra

Both canonical forms produce the same run identifier. The row-number difference says: 'within a run, the sequence column and the row number advance in lockstep, so their difference is constant.' The LAG-plus-cumulative-SUM says: 'a flag is 1 at each run boundary and 0 within a run; the cumulative sum is constant within a run and increments at each boundary.' Both reduce to the same observation: the run identifier is a function that changes only at the boundary between runs. The row-number form works when the boundary is 'sequence value not advancing by exactly one.' The LAG form works for any boundary condition that can be expressed as a CASE. The LAG form is strictly more general; the row-number form is shorter when the boundary is strict adjacency.

n minus rn is constant in a run because both sequences advance by one per row. Constant changes at every gap.
boundary flag is 1 at run boundaries, 0 within. Cumulative SUM is constant within a run, increments at boundaries.
row-number form is shorter for strict adjacency; LAG form generalizes to thresholds and value-change boundaries.

Variant	Boundary condition	Right form
Runs of consecutive integers	Strict +1 adjacency	Row-number difference (cleaner)
Runs of consecutive days	Strict +1 day	Row-number difference (cleaner)
Sessions within 30 minutes	Threshold gap	LAG plus cumulative SUM
Status-change runs	Value change (status flip)	LAG plus cumulative SUM
Conditional islands (status = 'active')	Value change plus per-row filter	LAG plus cumulative SUM with filter after

The closing summary

Close with a four-sentence wrap. 'This is gaps and islands. For strict adjacency I use the row-number difference; for threshold gaps or value-change boundaries I use LAG plus cumulative SUM. The two forms produce the same run identifier; the choice depends on whether the boundary is value-plus-one or a more general condition. For per-island filtering, the filter goes after the run identifier is computed, because filtering before would destroy the boundary signal. For scale, the dominant cost is the partition shuffle on the entity column; aligning source clustering with the query partition is the lever that matters.' Four sentences. Two forms, filter placement, scale. The shape generalizes to every variant of this question.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at a SaaS analytics company. The interviewer asks: 'Compute user session boundaries from an events table where a session is a sequence of events with no gap longer than thirty minutes. Then return the sessions with at least five events.'

You say: 'This is a gaps-and-islands variant with a threshold gap. The row-number difference does not apply because the boundary is not strict adjacency; I'll use LAG plus cumulative SUM.'

You write three CTEs: lagged (LAG to expose prev_event_time), flagged (CASE for is_new_session when no predecessor or gap exceeds 30 min), sessions (cumulative SUM of is_new_session for session_id). Then GROUP BY (user_id, session_id) with HAVING COUNT(*) >= 5.

You articulate the proof: 'is_new_session is 1 at every session boundary and 0 within a session. Cumulative SUM increments at each boundary and stays constant within a session, producing a unique session_id.'

You name the filter placement: 'I'm filtering with HAVING COUNT(*) >= 5 after the GROUP BY. WHERE would filter individual events; HAVING filters whole sessions, which is the right grain for this question.'

Follow-up: 'What if two events have the same event_time?' You say: 'I'd add event_id as a secondary ORDER BY for a deterministic tiebreaker; without it, the LAG can pick either row as the predecessor and the session_id becomes non-deterministic.'

Closing: 'The two canonical forms (row-number difference and LAG plus cumulative SUM) produce the same run identifier; the LAG form generalizes to threshold gaps, value-change boundaries, and conditional islands.'

KEY TAKEAWAYS

The row-number difference form only works when the sequence column advances by exactly one per row inside a run. Sessionization is threshold adjacency, not strict adjacency, so reach for LAG plus a cumulative SUM of a boundary flag instead.

Both canonical forms produce the same run identifier: a value that changes only at a run boundary. The row-number difference encodes the boundary as value-plus-one; the CASE expression in the LAG form encodes any boundary condition, which is why it generalizes to thresholds and status flips.

Compute the run identifier first, then filter. Filtering the source down to status = 'healthy' before the boundary detection silently merges runs separated by rows you removed. Filters above the run grain, such as dropping a whole user, stay safe.

Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW on the cumulative sum. The default RANGE frame treats tied timestamps as one peer group and hands them the same running total, which corrupts the run identifier.

Per-island thresholds belong in HAVING, after the GROUP BY on the run key. Add a deterministic tiebreaker such as ORDER BY event_time, event_id so same-timestamp rows pick the same predecessor on every run, and expect the partition shuffle on the entity column to dominate cost at scale.

Consecutive sequences break most candidates; the trick is a two-window difference

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: The ROW_NUMBER Difference Trick, Date Gaps vs Integer Gaps, Island Aggregates: Length, Start, End, Handling Ties and Duplicate Timestamps, Grouping by the Computed Island Key

Lesson Sections

The ROW_NUMBER Difference Trick (concepts: sqlLagLead)
Here is the question that recurs in interviews past the basics. 'Compute user sessions from an events table, where a session is a sequence of events with no gap longer than thirty minutes.' This is gaps and islands with a threshold instead of strict adjacency. The row-number difference trick from the beginner lesson does not apply directly; the gap is not 'value plus one,' it is 'value within some delta.' The LAG-plus-cumulative-SUM form generalizes to this case. Knowing when to reach for it, an
Date Gaps vs Integer Gaps (concepts: sqlLagLead)
The LAG-plus-cumulative-SUM form is three CTEs instead of two. LAG exposes the predecessor's value. A CASE expression flags rows that start a new run. A cumulative SUM of the flag produces a run identifier. Each CTE is one job; the query reads top-down as the story of the calculation. The canonical sessionization query Reading the query top-down The lagged CTE adds a prev_event_time column via LAG; for the first event per user, prev_event_time is NULL. The flagged CTE adds a boolean is_new_sessi
Island Aggregates: Length, Start, End (concepts: sqlLagLead)
The other common variant is conditional islands. The runs are not defined by adjacency at all; they are defined by a status or attribute being constant for a stretch of rows. 'Find contiguous periods where a subscription was active.' 'Find stretches where a server was healthy.' 'Find runs of days the stock closed above its 50-day moving average.' These are gaps and islands where 'consecutive' means 'sharing the same value' rather than 'adjacent in the sequence.' The LAG form handles this case cl
Handling Ties and Duplicate Timestamps (concepts: sqlHaving)
After the run_id is in place, the rest of the query is GROUP BY and aggregation. The interviewer at this level will probe whether you handle per-island thresholds and conditional aggregates correctly. The two most common probes: 'only return runs of at least N events' and 'compute statistics across the runs.' Each one tests where the threshold belongs in the query. 'Only return runs of at least N events' This is a per-island threshold. The filter applies to the aggregate (COUNT, SUM, MAX) after
Grouping by the Computed Island Key (concepts: sqlLagLead)
The closing escalation at this level covers three areas: performance on large tables, edge cases that ship to production, and the synthesis that ties the two canonical forms together. Hitting all three is what flips the verdict from hire to strong hire on this question. Performance: partition shuffles dominate Both canonical forms partition by the entity column (user_id, subscription_id) and order within the partition. The dominant cost on a distributed engine is the shuffle that co-locates each