DEV Community

Cover image for SQL Gaps & Islands: Sessionization, Streaks & Run-Length Patterns
Gowtham Potureddi
Gowtham Potureddi

Posted on

SQL Gaps & Islands: Sessionization, Streaks & Run-Length Patterns

#python #sql #dataengineering #interview

sql gaps and islands is the single most-asked "consecutive rows" family in senior data-engineering interviews — and the single most under-taught one in undergraduate SQL curricula. The same "identify runs of consecutive events" ask shows up in five different colours: sessionization ("group events into 30-minute sessions"), login streaks ("longest consecutive-day streak per user"), uptime windows ("longest window with status = 'up'"), price plateaus ("compress the price series into runs of unchanged values"), and CDC state transitions ("emit one row per contiguous state"). Every answer starts from the same two-line trick: subtract a running counter from an ordered attribute, group by the difference, and each group is one island. The interviewer wants to hear you say "row_number minus date equals island id" in the first sentence.

This guide is the mid-to-senior tour you wished existed the first time an interviewer asked you to write a sessionization sql query on the whiteboard, sketch sql streaks for a growth analytics dashboard, chase a consecutive rows sql bug in production, derive a sql session id for a Segment-style event stream, or design an sql run length encoding compression for a status-transition table. It walks through the classic date-island Tabibitosan trick with ROW_NUMBER and date arithmetic, the sessionization pattern with LAG(event_time) plus SUM(new_session) OVER and per-user partitioning, the LeetCode-flavoured consecutive-day streak problem with longest-vs-current framing and holiday tolerance, and the run-length encoding pattern with SUM(CASE WHEN prev <> curr THEN 1 ELSE 0 END) OVER plus the MATCH_RECOGNIZE alternative and the Postgres / Snowflake / BigQuery / SQL Server dialect matrix. Every section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

PipeCode blog header for SQL gaps and islands sessionization and streaks — bold white headline 'SQL Gaps & Islands' with subtitle 'Sessionization · Streaks · Run-Length Patterns' and a stylised island-id derivation card on a dark gradient with purple, green, orange, and blue accents and a small pipecode.ai attribution.

When you want hands-on reps immediately after reading, drill the gaps-and-islands practice library →, rehearse on window-function problems →, and sharpen the timing axis with time-series drills →.

On this page

1. Why gaps & islands matters in 2026

The "consecutive rows problem" family — sessionization, streaks, uptime, price plateaus, and every other run-of-events question a senior interviewer might ask

The one-sentence invariant: a sql gaps and islands question asks you to identify contiguous runs of rows that share some ordering property, and the canonical answer is always some form of "assign an island id, group by island id, aggregate per island". Once you internalise that "assign island id + group + aggregate" is the whole family, the consecutive-rows interview surface collapses into a two-step template you can write from muscle memory.

Five faces of the same problem.

  • Sessionization. Given an event stream, group consecutive events into sessions separated by a gap of inactivity (usually 30 minutes). Every product analytics tool — Google Analytics, Segment, Rudderstack, Snowplow, PostHog, Mixpanel — implements exactly this logic. The interviewer wants to see you write it in warehouse SQL, not paste an out-of-the-box definition.
  • Streaks. Given a table of user activity dates, find the longest consecutive-day streak per user (LeetCode's "Consecutive Numbers" and "Trips and Users" flavours). Growth teams live and die on daily-active retention, weekly streaks, workout streaks, learning-app streaks — all one query pattern.
  • Uptime / downtime windows. Given a table of status pings, find the longest window where status = 'up' (or the last outage of duration > 5 minutes). SRE dashboards and pager duty policies both consume this shape.
  • Price plateaus. Given a price time series with many unchanged values in a row, compress into runs of (start_ts, end_ts, price). Storage cost drops by 10-100× for slowly-changing values.
  • CDC state transitions. Given a slowly changing dimension type-2 stream, emit one row per contiguous state. Debezium, Airbyte, and Fivetran output this shape; downstream models consume it.

The two mental primitives.

  • Island id assignment. The mechanical trick that turns a sequence into groups. Two flavours: (a) subtract a running counter from the ordered attribute (login_date - ROW_NUMBER()); (b) cumulative-sum a "new group" flag (SUM(CASE WHEN gap > threshold THEN 1 ELSE 0 END) OVER (...)). Every gaps-and-islands answer uses one of these two.
  • Group + aggregate. Once you have an island id, GROUP BY user_id, island_id and aggregate — MIN(ts) as session start, MAX(ts) as session end, COUNT(*) as event count, etc. This half is boring; the whole trick is in the island-id derivation.

Why senior interviewers love this family.

  • It tests window-function fluency without being about ranking. Everyone can write ROW_NUMBER() for a leaderboard. Fewer candidates recognise that ROW_NUMBER() also works as a gap-detection primitive when subtracted from an ordered attribute.
  • It has a portable answer and a MATCH_RECOGNIZE answer. The interviewer probes whether you know both — the portable window-function recipe and the Snowflake/Oracle native syntax — and when to reach for each.
  • It's compositional. Once you can sessionize, streaks are trivial. Once you can do streaks, RLE is a variation. Once you can do RLE, uptime windows are the same query with a filter. A senior candidate walks through this progression without prompting.
  • It exposes multi-user partitioning discipline. Every answer must PARTITION BY user_id inside the window clauses. Candidates who forget the partition get wrong answers on the first test case — an instant red flag.

Where consecutive-rows queries land in your pipelines.

  • Product analytics. Every event stream is sessionized before it can be counted, deduped, or attributed. session_id is the atomic unit of product analytics.
  • Growth analytics. DAU/WAU/MAU retention, streak-based engagement metrics, funnel step deduplication — every metric that touches "consecutive days of activity" is a streaks query underneath.
  • Reliability / SRE. Uptime SLOs, outage duration histograms, incident post-mortems — every timeline reconstruction is an islands query with a status attribute.
  • Financial time series. Price plateaus, volatility clusters, order book state transitions, market-hours vs after-hours splits — every "runs of contiguous values" question is RLE.
  • CDC / SCD pipelines. Type-2 dimensions collapse consecutive unchanged rows into a single validity window using RLE. The valid_from / valid_to columns are computed exactly this way.

What senior interviewers actually probe.

  • Island-id derivation. Do you reach for ROW_NUMBER - date or SUM(new_group) OVER? Do you know when to use each? Can you say "date arithmetic works when the sequence is dense; cumulative sum works when the gap is a computed threshold" without prompting?
  • Sessionization threshold. Do you hard-code INTERVAL '30 minutes' or take it as a parameter? Do you know Segment / GA use 30 minutes by default? Do you handle timezone edge cases when the ETL runs across the DST boundary?
  • Streak-with-gap tolerance. Can you allow a 1-day miss without breaking the streak? Do you know how to pre-join a business-calendar table to exclude holidays and weekends from streak checks?
  • RLE compression math. Can you estimate the compression ratio for a slowly-changing series? Do you know when RLE beats DISTINCT ... LAG vs when a DISTINCT view is enough?
  • MATCH_RECOGNIZE vs portable. Do you know which warehouses ship MATCH_RECOGNIZE (Oracle, Snowflake) and which don't (Postgres, BigQuery, SQL Server)? Do you default to the portable LAG + SUM pattern for a multi-warehouse codebase?

Worked example — the "consecutive numbers" starter problem

Detailed explanation. The canonical whiteboard warm-up: given a logs(id, num) table, find every number that appears at least three times consecutively. This is LeetCode's "Consecutive Numbers" problem, and it's the fastest way to check that a candidate recognises the gaps-and-islands family. The two-line answer uses LAG twice or a ROW_NUMBER - RANK trick, and the interviewer wants to see whichever variant you like walked through cleanly.

Question. Given logs(id, num) where id is a dense ascending integer and num is any integer, write a query that returns every num that appears three or more times in a row.

Input.

id num
1 1
2 1
3 1
4 2
5 1
6 2
7 2

Code.

-- Portable — LAG twice, check equality
SELECT DISTINCT l.num AS ConsecutiveNums
FROM (
  SELECT
    num,
    LAG(num, 1) OVER (ORDER BY id) AS prev1,
    LAG(num, 2) OVER (ORDER BY id) AS prev2
  FROM logs
) l
WHERE l.num = l.prev1 AND l.num = l.prev2;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. LAG(num, 1) OVER (ORDER BY id) produces the previous row's num, ordered by id. LAG(num, 2) ... produces two rows back. Together, each row now knows its own value and the two before it.
  2. The WHERE l.num = l.prev1 AND l.num = l.prev2 predicate keeps only rows where the current value equals both the previous value and the one before that — a three-in-a-row match on the current row's timestamp.
  3. SELECT DISTINCT l.num deduplicates the matches. If the same value appears in four consecutive rows, the predicate matches on rows 3 and 4; DISTINCT collapses the duplicate output.
  4. The alternative form uses num - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) — the island-id trick — then groups by (num, island_id) and filters for COUNT(*) >= 3. Same answer, different mechanics.
  5. Rule of thumb: for a fixed-length "N in a row" check, LAG chains are more readable. For a variable-length "runs of X" query, the island-id trick generalises better.

Output.

ConsecutiveNums
1

Rule of thumb. LeetCode "Consecutive Numbers" is the gaps-and-islands warm-up — recognise it in 5 seconds, write the LAG chain in 30, then move on to the harder island-id derivation the interviewer really wants.

Worked example — sessionize an event stream (preview)

Detailed explanation. The sessionization interview is a gaps-and-islands question wearing a product-analytics costume. The pattern is: compute the time gap to the previous event with LAG(event_time), flag rows where the gap exceeds a threshold, cumulative-sum the flag to produce a session id. This preview walks the mechanics; Section 3 goes deep on late-data and per-user partitioning.

Question. Given an events(user_id, event_time) stream and a 30-minute inactivity threshold, write a query that assigns a session_id (integer, 0-indexed within each user) to every row.

Input.

user_id event_time
u1 2026-07-01 10:00
u1 2026-07-01 10:05
u1 2026-07-01 11:15
u1 2026-07-01 11:20

Code.

SELECT
  user_id,
  event_time,
  SUM(new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_id
FROM (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN EXTRACT(EPOCH FROM (event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time))) > 30 * 60
        THEN 1
      ELSE 0
    END AS new_session
  FROM events
) t;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Inner query — LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) yields the previous event's timestamp within the user. The first event of each user has LAG = NULL, and the epoch difference is NULL — treated as 0 by the CASE, so the first event stays in session 0.
  2. EXTRACT(EPOCH FROM (event_time - LAG(...))) converts the interval to seconds. > 30 * 60 is the 30-minute threshold — any gap larger flags a new session.
  3. CASE WHEN ... THEN 1 ELSE 0 END produces a 0/1 flag column called new_session. This is the "island-boundary" indicator.
  4. Outer query — SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) cumulatively sums the flags per user. The result increments by 1 at every gap larger than 30 minutes, producing a per-user 0-indexed session id.
  5. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the explicit window frame; some dialects default to it, but stating it explicitly avoids the "range vs rows" trap that trips senior candidates.

Output.

user_id event_time session_id
u1 2026-07-01 10:00 0
u1 2026-07-01 10:05 0
u1 2026-07-01 11:15 1
u1 2026-07-01 11:20 1

Rule of thumb. Sessionization is LAG + CASE + SUM OVER — three window primitives combined. Recognise the pattern in seconds; write the query in a minute.

Worked example — collapse a price series with RLE (preview)

Detailed explanation. Run-length encoding on a price series compresses consecutive unchanged values into one row per run. This preview shows the SUM-of-change-points trick; Section 5 goes deep on dialect variations and MATCH_RECOGNIZE alternatives.

Question. Given prices(ts, symbol, price) where price changes rarely, write a query that emits one row per (symbol, run) with the run's start_ts, end_ts, and price.

Input.

ts symbol price
1 AAPL 100
2 AAPL 100
3 AAPL 105
4 AAPL 105
5 AAPL 105
6 AAPL 100

Code.

WITH flagged AS (
  SELECT
    ts,
    symbol,
    price,
    CASE
      WHEN price <> LAG(price) OVER (PARTITION BY symbol ORDER BY ts)
        OR LAG(price) OVER (PARTITION BY symbol ORDER BY ts) IS NULL
        THEN 1
      ELSE 0
    END AS is_new_run
  FROM prices
),
grouped AS (
  SELECT
    symbol,
    price,
    ts,
    SUM(is_new_run) OVER (PARTITION BY symbol ORDER BY ts) AS run_id
  FROM flagged
)
SELECT
  symbol,
  price,
  MIN(ts) AS start_ts,
  MAX(ts) AS end_ts,
  COUNT(*) AS run_length
FROM grouped
GROUP BY symbol, price, run_id
ORDER BY start_ts;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. flagged CTE — for each row, compare the current price to the previous row's price (via LAG). If it differs (or if it's the first row), mark is_new_run = 1; otherwise 0. The special-case for NULL handles the very first row of the partition.
  2. grouped CTE — cumulative-sum the is_new_run flag per symbol. Every 1 increments the counter; every 0 preserves it. The result is a run_id that's constant within one run and increments at each change point.
  3. Outer SELECT — group by (symbol, price, run_id) to collapse each run into one row. MIN(ts) is the run start, MAX(ts) is the run end, COUNT(*) is the run length.
  4. Including price in the GROUP BY is mostly cosmetic — the run_id already implies the price — but keeps the plan simple and the intent explicit.
  5. Compression ratio: a series where 90% of rows are duplicates compresses ~10×. For slowly-changing prices or feature-flag rollouts, RLE cuts storage and query cost dramatically.

Output.

symbol price start_ts end_ts run_length
AAPL 100 1 2 2
AAPL 105 3 5 3
AAPL 100 6 6 1

Rule of thumb. RLE = flag change points with LAG + CASE, cumulative-sum to run_id, then GROUP BY run_id. Same three-step template as sessionization, different threshold.

Senior interview question on the gaps-and-islands mental model

A senior interviewer often opens with: "I hand you a table of user events over the last year and ask for a metric — pick one — that requires you to identify runs of consecutive activity. Walk me through your two-step template, and tell me what changes if the ask is sessionization vs streaks vs uptime windows."

Solution Using the "flag + cumulative sum" universal template 

Universal gaps-and-islands template — three steps

Step 1 — Order the rows within each partition.
  PARTITION BY <entity> ORDER BY <time-or-sequence>

Step 2 — Assign an island id (two flavours).
  Flavour A — dense sequence subtraction (streaks, dense-date islands):
    island_id = ordered_attribute - ROW_NUMBER() OVER (PARTITION BY entity ORDER BY ordered_attribute)

  Flavour B — cumulative sum of change flag (sessions, RLE, uptime windows):
    is_new = CASE WHEN <boundary condition> THEN 1 ELSE 0 END
    island_id = SUM(is_new) OVER (PARTITION BY entity ORDER BY ordered_attribute)

Step 3 — Group by island id and aggregate.
  GROUP BY entity, island_id
  aggregates → MIN(ts) AS start, MAX(ts) AS end, COUNT(*) AS length, ...
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Ask Flavour Boundary condition Aggregate
Login streaks A consecutive dates (dense) MIN(date), MAX(date), COUNT(*) as streak length
Sessionization B gap > 30 minutes MIN(ts) as session_start, COUNT(*) as event count
Uptime windows B status changes from up to down or vice versa MIN(ts), MAX(ts), duration
Price plateaus (RLE) B price <> LAG(price) MIN(ts), MAX(ts), price, COUNT(*) as run length
Consecutive-N check B value = LAG(value) filter to COUNT(*) >= N

The universal template turns "which query do I write?" from a taste question into a mechanical checklist. Pick the partition, pick the ordering, pick the flavour, apply the aggregate — the whole query falls out.

Output:

Ask Query length Time to whiteboard
Streak per user 8-12 lines ~2 min
Sessionization 12-18 lines ~3 min
Uptime windows 15-25 lines ~4 min
Price RLE 18-25 lines ~4 min
Consecutive-N 6-8 lines ~1 min

Why this works — concept by concept:

  • Two flavours cover the space — every gaps-and-islands question is either "dense sequence" (streaks, calendars) or "computed boundary" (sessions, RLE, uptime). Flavour A uses ROW_NUMBER subtraction; Flavour B uses cumulative sum of a flag. There is no third pattern.
  • PARTITION BY entity is non-negotiable — multi-user data always requires per-user partitioning. Skipping it produces a global island id that mixes users, which is a wrong-answer buffer that the interviewer catches immediately.
  • ORDER BY inside the window matters — the ordering determines what "consecutive" means. Sessionization orders by event_time; RLE orders by ts; streaks order by date. Get the ordering wrong and the island id is meaningless.
  • The aggregate is boring by design — once the island id is right, the aggregation is standard GROUP BY + MIN + MAX + COUNT. Interviewers spend 90% of the follow-up time on island-id derivation and 10% on the aggregate — mirror that ratio in your explanation.
  • Cost — the template is O(N log N) per partition for the ORDER BY inside the window, plus O(N) for the aggregate. On a 100M-row table with 1M users, that's ~1B operations — 10-30 seconds on modern warehouses. If it's too slow, pre-aggregate at ingestion or push partitions into a clustering key.

SQL
Topic — gaps-and-islands
Gaps and islands problems

Practice →

SQL Topic — window functions Window function drills

Practice →

2. Classic date-island pattern

consecutive rows sql in its purest form — the date - ROW_NUMBER Tabibitosan trick that turns a dense sequence into island groups

The mental model in one line: when a dense-date sequence is punctuated by gaps, subtracting a per-partition ROW_NUMBER() from the ordered attribute produces a constant per island — group by that constant and you have your runs. Once you say "date minus row_number equals island id" out loud, the classic date-island interview surface collapses to a five-line SQL query.

Visual diagram of the classic date-island pattern — left an input table of consecutive login dates with a row_number column, a purple arrow labelled 'date - row_number', right a two-island output table with island-id / first_day / last_day; a small annotation about the Tabibitosan trick; on a light PipeCode card.

Why the trick works.

  • Dense sequences advance in lockstep. If a user logs in on 2026-07-01, 2026-07-02, 2026-07-03, and the ordered ROW_NUMBER() is 1, 2, 3, then date - row_number is 2026-06-30 for all three rows. The difference is constant inside an island.
  • A gap breaks the lockstep. If the next login is on 2026-07-06 and its row_number is 4, then date - row_number = 2026-07-02 — a different island id. The gap moved the row_number ahead of the date.
  • The island id has no meaning on its own — it's just a group key. You never expose it in the output; you only use it in GROUP BY. Aggregations recover the human-readable boundaries.

The Tabibitosan name. The trick is credited to a Japanese SQL blog post by Aketi Jyuuzou circa 2008. "Tabibitosan" translates to "arithmetic problem for travellers," and refers to the classic maths puzzle "two travellers walking at different speeds meet in the same place — where and when?" — which is the same "lockstep + gap" mental model.

Dialect variations on the date arithmetic.

  • Postgres. login_date - (ROW_NUMBER() OVER (...) || ' days')::interval — cast the row number to an interval. Or login_date - INTERVAL '1 day' * ROW_NUMBER() OVER (...).
  • Snowflake. DATEADD(day, -ROW_NUMBER() OVER (...), login_date).
  • BigQuery. DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (...) DAY).
  • SQL Server. DATEADD(day, -ROW_NUMBER() OVER (...), login_date).
  • MySQL 8. login_date - INTERVAL ROW_NUMBER() OVER (...) DAY.

Alternative: use integer difference for weekly or monthly islands.

  • Weekly islands: WEEK(login_date) - ROW_NUMBER() OVER (...) — same trick, different granularity.
  • Monthly islands: MONTH(login_date) + YEAR(login_date) * 12 - ROW_NUMBER() OVER (...) — 12-month wrap.
  • Custom period islands: any monotonically-increasing integer works. EXTRACT(EPOCH FROM date) for second-granularity; EXTRACT(EPOCH FROM date) / 3600 for hour-granularity.

Handling non-daily grids.

  • Weekends off. Pre-filter to only Mon-Fri dates before applying the trick. Or pre-join a business-calendar table and use business_day_number instead of login_date.
  • Holidays off. Same as weekends — pre-join a holiday calendar and use the working_day_number as the ordered attribute.
  • Custom activity days. Any dense-integer attribute works. If a user must log in on the 1st, 15th, or 30th of each month to count, encode those dates into a dense sequence first.

Multi-user partitioning.

  • Always PARTITION BY user_id inside the ROW_NUMBER() window. Without the partition, all users share one row_number and the island ids mix across users.
  • The GROUP BY after the subtraction must include user_id too — same reason.
  • The final SELECT always projects user_id alongside the island columns.

Handling duplicates in the source data.

  • Distinct pre-step. If a user can have multiple rows on the same date, SELECT DISTINCT user_id, login_date FROM logins first. Otherwise, ROW_NUMBER() sees duplicates as separate rows and breaks the lockstep.
  • DENSE_RANK. An alternative — login_date - DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date) — tolerates duplicates because DENSE_RANK assigns the same rank to ties. Same island id for a duplicated date. Read DENSE_RANK when the source may have duplicates.

Common sql gaps and islands date-pattern gotchas.

  • Wrong ORDER BY. ORDER BY login_date inside ROW_NUMBER produces the right island ids only if the dates are dense. ORDER BY user_id, login_date in a global window is a wrong-answer bug that mixes users.
  • Missing PARTITION BY. Global row_number across users mixes island ids. Every senior interviewer catches this on the second test case.
  • Off-by-one on the aggregation. The island start is MIN(login_date); the end is MAX(login_date); the length is MAX - MIN + 1 (inclusive of both endpoints). Reporting MAX - MIN under-counts by one — a classic streak-length bug.

Worked example — dense-date island detection

Detailed explanation. The teaching problem: given a logins(user_id, login_date) table where a user may log in on many consecutive days, find every user's login islands with start date, end date, and length. This is the canonical dense-date interview ask.

Question. Given logins(user_id, login_date) with distinct rows per (user_id, login_date), write a query that returns (user_id, island_start, island_end, island_length) for every login island of every user.

Input.

user_id login_date
u1 2026-07-01
u1 2026-07-02
u1 2026-07-03
u1 2026-07-06
u1 2026-07-07
u2 2026-07-05
u2 2026-07-06

Code.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM logins
),
islanded AS (
  SELECT
    user_id,
    login_date,
    login_date - (rn || ' days')::interval AS island_id
  FROM numbered
)
SELECT
  user_id,
  MIN(login_date)::date AS island_start,
  MAX(login_date)::date AS island_end,
  COUNT(*) AS island_length
FROM islanded
GROUP BY user_id, island_id
ORDER BY user_id, island_start;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. numbered CTE — per user, order the login dates ascending and assign rn = 1, 2, 3, .... The partition ensures each user gets its own row_number sequence.
  2. islanded CTE — compute island_id = login_date - rn days. For a consecutive run 2026-07-01, 2026-07-02, 2026-07-03 with rn = 1, 2, 3, the island_id is 2026-06-30 for all three rows. The next login on 2026-07-06 with rn = 4 gives island_id = 2026-07-02 — a different island.
  3. Outer SELECT — GROUP BY user_id, island_id collapses each island into one row. MIN(login_date) recovers the first date; MAX(login_date) the last; COUNT(*) the run length.
  4. The ORDER BY user_id, island_start sorts the output for readability. Not strictly required — the caller can sort — but always nice on a whiteboard.
  5. The ::date cast defends against timezone drift when login_date is a timestamp; on a pure date column, it's a no-op.

Output.

user_id island_start island_end island_length
u1 2026-07-01 2026-07-03 3
u1 2026-07-06 2026-07-07 2
u2 2026-07-05 2026-07-06 2

Rule of thumb. Dense-date islands are three CTEs and one GROUP BY. Write the ROW_NUMBER, subtract from the date, group, aggregate — the whole pattern fits on a whiteboard in five minutes.

Worked example — handling weekend gaps (business-day islands)

Detailed explanation. A common follow-up: "your streak query treats weekends as breaks — how do you make weekends not count?" The answer is to compress the dense-date grid down to only business days first, then apply the standard island-id trick against the compressed grid. This is where a pre-joined business-calendar table earns its keep.

Question. Given logins(user_id, login_date) and a business_days(date, business_day_num) calendar table where business_day_num is a dense integer over Mon-Fri only, write a query that returns login islands treating weekends as "not a break."

Input (logins).

user_id login_date
u1 2026-07-06 (Mon)
u1 2026-07-07 (Tue)
u1 2026-07-10 (Fri)
u1 2026-07-13 (Mon)
u1 2026-07-14 (Tue)

Input (business_days).

date business_day_num
2026-07-06 1
2026-07-07 2
2026-07-08 3
2026-07-09 4
2026-07-10 5
2026-07-13 6
2026-07-14 7

Code.

WITH joined AS (
  SELECT
    l.user_id,
    l.login_date,
    b.business_day_num
  FROM logins l
  JOIN business_days b ON b.date = l.login_date
),
numbered AS (
  SELECT
    user_id,
    login_date,
    business_day_num,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM joined
),
islanded AS (
  SELECT
    user_id,
    login_date,
    business_day_num - rn AS island_id
  FROM numbered
)
SELECT
  user_id,
  MIN(login_date)::date AS island_start,
  MAX(login_date)::date AS island_end,
  COUNT(*) AS active_days
FROM islanded
GROUP BY user_id, island_id
ORDER BY user_id, island_start;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. joined CTE — enrich each login with its business_day_num from the calendar. Weekends are absent from the calendar, so any weekend login (if it exists) is dropped by the join. If you want weekend logins to still count as activity but not break the streak, adjust the join to be a left join and coalesce a synthetic business-day number.
  2. numbered CTE — same as before: per user, order by login_date and produce rn.
  3. islanded CTE — business_day_num - rn is now the island id. Because business_day_num skips weekends, two logins on consecutive business days (Fri and Mon) have business_day_num differing by 1 — the streak continues.
  4. Outer SELECT — group by island_id, take MIN/MAX/COUNT. island_length here is renamed to active_days to emphasise that it counts activity, not calendar days.
  5. Trade-off: the query now depends on a business_days table. Every warehouse should have one — Snowflake's SYSTEM$GENERATE_CALENDAR, dbt's dbt_date package, or a hand-maintained table. If yours doesn't, generate one from generate_series (Postgres) or a recursive CTE (SQL Server).

Output.

user_id island_start island_end active_days
u1 2026-07-06 2026-07-10 3
u1 2026-07-13 2026-07-14 2

Rule of thumb. For any streak or island query where weekends or holidays should not break the run, pre-join a business-day calendar and use the calendar's dense integer as the ordered attribute. The trick is the same; the grid changes.

Worked example — first and last event per island

Detailed explanation. A common variation: instead of aggregating an island to its length, ship the raw first and last events per island for downstream joins. This is what CDC pipelines do to compute type-2 slowly-changing dimension valid_from / valid_to. The trick is the same, plus a FIRST_VALUE / LAST_VALUE window pair to expose the endpoints on every row.

Question. Given logins(user_id, login_date) and a derived island id, write a query that returns every login row enriched with island_start and island_end as extra columns, without collapsing.

Input.

user_id login_date
u1 2026-07-01
u1 2026-07-02
u1 2026-07-03
u1 2026-07-06

Code.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM logins
),
islanded AS (
  SELECT
    user_id,
    login_date,
    login_date - (rn || ' days')::interval AS island_id
  FROM numbered
)
SELECT
  user_id,
  login_date,
  MIN(login_date) OVER (PARTITION BY user_id, island_id)::date AS island_start,
  MAX(login_date) OVER (PARTITION BY user_id, island_id)::date AS island_end
FROM islanded
ORDER BY user_id, login_date;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The first two CTEs are identical to the earlier example — produce island_id per row using the row_number-subtracted-from-date trick.
  2. Outer SELECT — replace the GROUP BY + MIN/MAX aggregation with MIN() OVER (PARTITION BY user_id, island_id) and MAX() OVER (...) — window aggregates that don't collapse the row.
  3. Every row now has three interesting columns: its own login_date, the island's start, and the island's end. This shape is exactly what a downstream join wants — you can JOIN ... USING (user_id, island_id) or WHERE login_date = island_end to grab the "last event per island."
  4. The PARTITION BY user_id, island_id inside the window is the critical piece. Without both, either the users mix or the islands mix.
  5. Cost: two OVER clauses over the same partition are typically fused into one window pass by modern planners. On a 100M row table, expect the same runtime as a single-window query.

Output.

user_id login_date island_start island_end
u1 2026-07-01 2026-07-01 2026-07-03
u1 2026-07-02 2026-07-01 2026-07-03
u1 2026-07-03 2026-07-01 2026-07-03
u1 2026-07-06 2026-07-06 2026-07-06

Rule of thumb. When you need the raw rows enriched with island boundaries — CDC, retention join, cohort assignment — swap the GROUP BY + MIN/MAX for MIN() OVER (PARTITION BY ..., island_id) window aggregates. Same island-id logic; different aggregation shape.

Senior interview question on multi-user island detection

A senior interviewer might ask: "Given a logins(user_id, login_date) table with 500M rows and 10M users, write the query that returns every user's longest login streak, and estimate the plan's cost. Show me both the standard Tabibitosan approach and how you'd optimise if the table is that large."

Solution Using per-user island id + aggregate + top-1 join 

-- Step 1 — assign island ids per user
WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM logins
),
islanded AS (
  SELECT
    user_id,
    login_date,
    login_date - (rn || ' days')::interval AS island_id
  FROM numbered
),
-- Step 2 — aggregate to island length per (user, island)
per_island AS (
  SELECT
    user_id,
    island_id,
    MIN(login_date)::date AS island_start,
    MAX(login_date)::date AS island_end,
    COUNT(*) AS streak_length
  FROM islanded
  GROUP BY user_id, island_id
),
-- Step 3 — rank per user, keep only the longest island
ranked AS (
  SELECT
    user_id,
    island_start,
    island_end,
    streak_length,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY streak_length DESC, island_end DESC) AS rn
  FROM per_island
)
SELECT
  user_id,
  island_start AS longest_streak_start,
  island_end AS longest_streak_end,
  streak_length AS longest_streak_days
FROM ranked
WHERE rn = 1;
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Step What runs Rows out (approx)
1 ROW_NUMBER per user 500M
2 GROUP BY user_id, island_id ~50M islands (assuming ~5 islands/user avg)
3 ROW_NUMBER per user by streak length 50M with rank
4 Filter rn = 1 10M (one per user)

The three-step pattern reduces 500M raw rows down to 10M output rows (one per user) with the longest streak per user. Every step is a standard window-function pass — no self-joins, no cross-joins, no correlated subqueries.

Output:

user_id longest_streak_start longest_streak_end longest_streak_days
u1 2026-07-01 2026-07-03 3
u2 2026-05-15 2026-05-19 5
u3 2026-06-10 2026-06-10 1

Why this works — concept by concept:

  • PARTITION BY user_id everywhere — every window function in every CTE partitions by user_id. Skipping it mixes users. The row_number, the min/max, and the rank all need the partition.
  • Aggregation before ranking — the per_island CTE collapses each island to one row before the final ranking. Ranking 500M row-level records is dramatically more expensive than ranking 50M island-level records.
  • Tiebreaker on island_end — if a user has two islands of the same maximum length, ORDER BY streak_length DESC, island_end DESC keeps the more recent one. Interviewers appreciate seeing a tie-break argument even if the test data doesn't hit it.
  • ROW_NUMBER = 1 vs LIMIT — for the "top-1 per group" pattern, ROW_NUMBER() OVER (PARTITION BY ...) = 1 is the portable idiom. DISTINCT ON (user_id) is a shorter Postgres-only equivalent; QUALIFY ROW_NUMBER() = 1 is Snowflake/BigQuery shorthand.
  • Cost — the plan is three window scans + one aggregate. On a 500M-row table with 10M users, the dominant cost is the first ROW_NUMBER (sort-based, O(N log N) per partition). Clustering the table by user_id reduces the sort cost per partition; on Snowflake, CLUSTER BY (user_id, login_date) accelerates automatic clustering to keep this partition-friendly.

SQL
Topic — gaps-and-islands
Gaps and islands drills

Practice →

SQL Topic — window functions (medium) Window function problems (SQL, medium)

Practice →

3. Sessionization with 30-min inactivity

sessionization sql is the flagship gaps-and-islands application — group event streams into sessions using LAG + SUM(new_session) OVER

The mental model in one line: sessionization turns an event stream into per-user sessions by flagging rows where the gap to the previous event exceeds an inactivity threshold, then cumulative-summing the flag to produce a per-user session id. Once you say "LAG for the gap, CASE for the flag, SUM OVER for the id," the sessionization interview surface reduces to a three-line window recipe.

Visual diagram of 30-minute sessionization — left an event stream with timestamps and inactivity gaps flagged, a purple arrow labelled 'LAG + SUM(new_session) OVER', right a sessionized output with session_id column; a small annotation about the 30-min inactivity rule and per-user partitioning; on a light PipeCode card.

The canonical sessionization contract.

  • Definition. A session is a maximal run of events from one user where consecutive events are separated by no more than the inactivity threshold. The first event of the day starts a new session; a gap greater than the threshold starts another new session.
  • Standard threshold. 30 minutes. Google Analytics, Segment, Rudderstack, and Snowplow all default to 30. Some product tools default to 60 minutes for lower-frequency apps.
  • Session id. Historically opaque — a UUID or a per-user monotonically-increasing integer. The integer form is what warehouse SQL usually produces; a UUID form is what real-time systems assign.
  • Session boundaries. session_start_ts (first event ts), session_end_ts (last event ts), event_count, session_duration_seconds, first_page, last_page, etc.

The three-line pattern.

-- Step 1 — gap to previous event
LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_ts

-- Step 2 — new-session flag when gap > threshold
CASE
  WHEN event_time - prev_ts > INTERVAL '30 minutes' OR prev_ts IS NULL
    THEN 1 ELSE 0
END AS new_session

-- Step 3 — cumulative-sum the flag to session id
SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
Enter fullscreen mode Exit fullscreen mode

Chained together in a CTE or subquery, this is the whole primitive.

Choosing the threshold.

  • 30 min. Google Analytics standard. Good for consumer web and mobile apps where a user goes to lunch or answers a Slack ping.
  • 1 hour. Loose. Good for long-attention apps like video streaming, coding editors, IDEs.
  • 10 minutes. Tight. Good for high-frequency apps like IoT device dashboards or trading terminals.
  • Custom. Some teams use a data-driven threshold — histogram all inter-event gaps and pick the elbow at the 95th percentile. If your sessions look wrong at 30 min, plot the histogram first.

Timezone and DST handling.

  • Store event_time as UTC. Sessionization operates on event_time — the timezone of the user rarely matters for session boundaries.
  • If the ETL runs across a DST fall-back (2 AM twice), sessions can wrap the double hour. Because we compare against UTC, this is invisible — always store UTC and problem solved.
  • If a downstream report needs "session started before 6 PM local time" — convert session_start_ts to the user's timezone at the report level, not in the sessionization step.

Late-arriving events.

  • Real-world event streams have late events. A mobile app buffers events offline and flushes when the network returns; a server-side event goes through a queue with variable latency.
  • Watermark. Warehouse pipelines usually sessionize a rolling window (last 7 days) and re-sessionize on each run. Late events that arrive within the window are captured; older events are dropped.
  • Idempotent re-sessionization. The window function is deterministic given the input rows — re-running the query produces the same session ids for the same rows. Downstream consumers see stable ids as long as the input is stable.
  • UUID sessions. If you need stable ids across ETL runs, hash user_id and session_start_ts to a UUID: MD5(user_id || CAST(session_start_ts AS TEXT)). Deterministic, stable, but changes if the session start moves due to late data.

Per-user partitioning is non-negotiable.

  • Global session id. Wrong. Without PARTITION BY user_id in LAG and SUM(...) OVER, events from different users can "close" each other's sessions.
  • Per-user session id. Right. PARTITION BY user_id in every window function ensures session ids are computed independently per user.
  • Cross-user attribution. Some teams want a "global session ordinal" too — the Nth session across the whole system. That's a separate window pass; keep the per-user primitive clean first.

Session-level aggregations.

Once you have session_id, aggregations are straightforward:

SELECT
  user_id,
  session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  MAX(event_time) - MIN(event_time) AS session_duration,
  COUNT(*) AS event_count,
  MIN(page_url) FILTER (WHERE event_type = 'pageview') AS landing_page
FROM sessionized
GROUP BY user_id, session_id;
Enter fullscreen mode Exit fullscreen mode

Common sessionization sql interview probes.

  • "Why LAG and not LEAD?" — LAG references the previous event; the boundary flag fires when the current event is far from the previous one. LEAD would work symmetrically (fire on the last event of a session), but LAG is the canonical direction.
  • "What if the first event has no previous?" — LAG returns NULL. Handle explicitly in the CASE (OR prev_ts IS NULL) so the first event starts session 0.
  • "How do you handle multi-user tables?" — PARTITION BY user_id everywhere. The interviewer wants to hear it in the first sentence.
  • "How do you re-sessionize when late data arrives?" — recompute over a rolling window (7 days or 30 days). Assume late events within the window; drop older ones.
  • "What's the difference between session id and session UUID?" — the id is a per-user integer, the UUID is a global-unique identifier. Warehouse SQL usually produces the integer; downstream consumers convert to UUID via a deterministic hash.

Worked example — Segment-style event stream sessionization

Detailed explanation. The most-asked sessionization ask in a product-analytics interview: given a Segment-style events(user_id, event_time, event_name, page_url) stream, compute a session_id per user and per-session aggregations. This is the core pipeline any Segment / Rudderstack / Snowplow user has written.

Question. Given events(user_id, event_time, event_name, page_url) and a 30-minute inactivity threshold, produce a sessions table with (user_id, session_id, session_start, session_end, event_count, landing_page). Show both the sessionization CTE and the aggregation.

Input.

user_id event_time event_name page_url
u1 2026-07-01 10:00 page_view /home
u1 2026-07-01 10:05 click /home
u1 2026-07-01 10:20 page_view /pricing
u1 2026-07-01 11:15 page_view /home
u1 2026-07-01 11:20 click /home

Code.

WITH flagged AS (
  SELECT
    user_id,
    event_time,
    event_name,
    page_url,
    CASE
      WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) > INTERVAL '30 minutes'
        OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
        THEN 1
      ELSE 0
    END AS new_session
  FROM events
),
sessionized AS (
  SELECT
    user_id,
    event_time,
    event_name,
    page_url,
    SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM flagged
)
SELECT
  user_id,
  session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  MAX(event_time) - MIN(event_time) AS session_duration,
  COUNT(*) AS event_count,
  MIN(page_url) FILTER (WHERE event_name = 'page_view') AS landing_page
FROM sessionized
GROUP BY user_id, session_id
ORDER BY user_id, session_id;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. flagged CTE — compute new_session = 1 when the gap to the previous event exceeds 30 minutes, or when it's the first event of the user. The IS NULL branch handles the first event.
  2. sessionized CTE — cumulative-sum the flag per user. Session 0 covers the events before the first gap; session 1 covers the next block; etc.
  3. Final SELECT — group by (user_id, session_id) and aggregate. MIN(event_time) is the session start; MAX(event_time) the end. MIN(page_url) FILTER (WHERE event_name = 'page_view') picks the first pageview URL as the landing page.
  4. The FILTER clause is ANSI standard (Postgres, Snowflake, BigQuery); SQL Server uses MIN(CASE WHEN event_name = 'page_view' THEN page_url END) as the equivalent. Same effect.
  5. Extend with more per-session aggregates as needed — total revenue, exit page, campaign source, etc. The pattern generalises without re-sessionizing.

Output.

user_id session_id session_start session_end session_duration event_count landing_page
u1 0 2026-07-01 10:00 2026-07-01 10:20 00:20:00 3 /home
u1 1 2026-07-01 11:15 2026-07-01 11:20 00:05:00 2 /home

Rule of thumb. Sessionization is one CTE for the flag, one CTE for the id, one SELECT for the aggregation. Three tiny queries; one whole pipeline.

Worked example — configurable-threshold sessionization macro

Detailed explanation. In a real analytics stack, the threshold is a parameter — 30 min for consumer apps, 60 min for long-attention apps, 10 min for high-frequency apps. A dbt macro takes the threshold as an argument and generates the correct SQL.

Question. Write a dbt macro sessionize(events_relation, user_col, ts_col, threshold_minutes=30) that emits sessionization SQL parameterised on the threshold. Show the macro and one invocation.

Input. (Same as above — user_id, event_time, event_name, page_url.)

Code.

-- macros/sessionize.sql
{% macro sessionize(events_relation, user_col='user_id', ts_col='event_time', threshold_minutes=30) %}
  WITH flagged AS (
    SELECT
      *,
      CASE
        WHEN {{ ts_col }} - LAG({{ ts_col }}) OVER (PARTITION BY {{ user_col }} ORDER BY {{ ts_col }})
             > INTERVAL '{{ threshold_minutes }} minutes'
          OR LAG({{ ts_col }}) OVER (PARTITION BY {{ user_col }} ORDER BY {{ ts_col }}) IS NULL
          THEN 1
        ELSE 0
      END AS new_session
    FROM {{ events_relation }}
  )
  SELECT
    *,
    SUM(new_session) OVER (PARTITION BY {{ user_col }} ORDER BY {{ ts_col }}) AS session_id
  FROM flagged
{% endmacro %}

-- usage in a model
WITH sessionized AS (
  {{ sessionize(ref('events'), 'user_id', 'event_time', 30) }}
)
SELECT
  user_id,
  session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  COUNT(*) AS event_count
FROM sessionized
GROUP BY user_id, session_id;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The macro accepts the events relation, the user column name, the timestamp column name, and the threshold in minutes. Defaults match the Segment convention.
  2. Inside the macro, the sessionization primitive is the same LAG + CASE + SUM OVER pattern. The threshold is templated into the INTERVAL '{{ threshold_minutes }} minutes' clause.
  3. SELECT * inside the CTE forwards every downstream column — event_name, page_url, revenue, etc. — through the sessionization pass. Callers don't have to enumerate columns.
  4. Usage — call {{ sessionize(ref('events'), ...) }} inside a model. The compiled SQL is a plain CTE that any warehouse can execute.
  5. Adapting for BigQuery — replace INTERVAL '{{ threshold_minutes }} minutes' with INTERVAL {{ threshold_minutes }} MINUTE. Add a dispatch on target.type to pick the right syntax for every warehouse.

Output. (Same as the raw sessionization query — one row per (user, session) pair.)

Rule of thumb. Parameterise the threshold from day one. Hard-coding 30 minutes leads to code duplication when a new app onboards with a different threshold. A macro pays for itself the first time you need a second value.

Worked example — session close on explicit sign-out

Detailed explanation. Some products close sessions on explicit user actions — sign-out, tab-close, "end conversation." The pattern extends the boundary flag to include the explicit close: any row where event_name = 'sign_out' starts a new session on the next event.

Question. Given events(user_id, event_time, event_name) where event_name = 'sign_out' explicitly ends a session, write sessionization SQL that respects both the 30-min inactivity rule and the sign-out event.

Input.

user_id event_time event_name
u1 10:00 page_view
u1 10:05 sign_out
u1 10:07 page_view
u1 10:10 click

Code.

WITH flagged AS (
  SELECT
    user_id,
    event_time,
    event_name,
    CASE
      WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) > INTERVAL '30 minutes'
        OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
        OR LAG(event_name) OVER (PARTITION BY user_id ORDER BY event_time) = 'sign_out'
        THEN 1
      ELSE 0
    END AS new_session
  FROM events
)
SELECT
  user_id,
  event_time,
  event_name,
  SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM flagged
ORDER BY user_id, event_time;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The CASE now has three branches — inactivity gap, first event, and "previous event was sign_out." Any of them flags a new session.
  2. LAG(event_name) looks at the previous event's name. If it was sign_out, the current event starts a new session — even if the gap to the previous is 2 minutes.
  3. Note that the sign_out event itself belongs to the ending session, not the new one. It's the next event after sign_out that opens a new session.
  4. If the product wants "sign_out ends the session and the next event opens a new one immediately," this is correct. If it wants "sign_out itself is the last event of the ending session but the next event stays in the same session," drop the LAG branch.
  5. Generalise: any explicit boundary event (page_close, conversation_end, "start new task") can be modelled by adding a LAG check on the boundary event name.

Output.

user_id event_time event_name session_id
u1 10:00 page_view 0
u1 10:05 sign_out 0
u1 10:07 page_view 1
u1 10:10 click 1

Rule of thumb. For any product with explicit session-close semantics, add a LAG(event_name) = 'boundary_event' check to the new-session flag. The rest of the pipeline stays the same.

Senior interview question on session-level attribution

A senior interviewer might ask: "Given a Segment event stream, sessionize with 30-min inactivity, and then attribute the last-touch campaign per session and per user. Walk me through the query and explain how you'd handle a user whose last-touch campaign came from a session 30 days ago."

Solution Using sessionize + last-touch join + campaign attribution 

WITH flagged AS (
  SELECT
    user_id,
    event_time,
    event_name,
    utm_campaign,
    CASE
      WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) > INTERVAL '30 minutes'
        OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
        THEN 1
      ELSE 0
    END AS new_session
  FROM events
),
sessionized AS (
  SELECT
    user_id,
    event_time,
    event_name,
    utm_campaign,
    SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM flagged
),
per_session AS (
  SELECT
    user_id,
    session_id,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    COUNT(*) AS event_count,
    -- Last touch = the last non-null utm_campaign in the session
    (ARRAY_AGG(utm_campaign ORDER BY event_time DESC)
      FILTER (WHERE utm_campaign IS NOT NULL))[1] AS last_touch_campaign
  FROM sessionized
  GROUP BY user_id, session_id
),
per_user_last_touch AS (
  SELECT
    user_id,
    session_id,
    last_touch_campaign,
    -- Fill the last-touch forward across sessions
    LAST_VALUE(last_touch_campaign IGNORE NULLS) OVER (
      PARTITION BY user_id
      ORDER BY session_start
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS user_last_touch_campaign,
    session_start,
    session_end,
    event_count
  FROM per_session
)
SELECT * FROM per_user_last_touch
ORDER BY user_id, session_start;
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Step What runs Output shape
1 Sessionize the event stream Same rows plus session_id
2 Per-session aggregate One row per (user, session) with last_touch_campaign
3 Forward-fill last-touch across sessions Every session sees its user's most recent non-null campaign
4 Final SELECT Session-level output with both session and user attribution

The three-step attribution pipeline sessionizes, aggregates to session-level, then forward-fills the last non-null campaign across a user's session history. The forward-fill is a LAST_VALUE IGNORE NULLS — the same trick that GA's last-non-direct-click attribution uses.

Output:

user_id session_id last_touch_campaign user_last_touch_campaign session_start
u1 0 google_ads google_ads 2026-07-01
u1 1 NULL google_ads 2026-07-05
u1 2 facebook_ads facebook_ads 2026-07-10

Why this works — concept by concept:

  • Sessionize once, aggregate once, forward-fill once — the three-step pipeline mirrors the three-step gaps-and-islands universal template. Every extension (attribution, funnel, retention) reads the sessionized shape and adds one aggregation step.
  • FILTER + ARRAY_AGG for last non-null — the ARRAY_AGG(... ORDER BY ... DESC) FILTER (WHERE ... IS NOT NULL) trick picks the most recent non-null value inside a session. Portable to Postgres, Snowflake, BigQuery. SQL Server uses MAX(...) OVER (ORDER BY ...) with a case statement equivalent.
  • LAST_VALUE IGNORE NULLS for forward fill — the ANSI standard for "carry the last non-null value forward" — Postgres 16+, Snowflake, BigQuery, SQL Server 2022+ all ship it. The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame is required — without it, LAST_VALUE reads the whole partition and returns the last row's value instead of the running last.
  • Cross-session state without joins — the LAST_VALUE OVER primitive replaces a self-join that would otherwise be O(N²). Window functions are the analytics engineer's go-to when a self-join is tempting.
  • Cost — three window passes plus one aggregate. On a 1B-row event table with 10M users, the sessionization pass is dominant (O(N log N)); the aggregation is O(N); the forward-fill is O(N). Total plan: a few window scans, no joins, very cache-friendly. Modern warehouses run this in single-digit minutes on billion-row inputs.

SQL
Topic — time-series
Time-series analytics drills

Practice →

SQL Topic — interval processing Interval processing problems

Practice →

4. Consecutive-day streaks

sql streaks — the LeetCode-flavoured "longest consecutive-day streak per user" problem, plus current-streak framing and gap tolerance

The mental model in one line: a consecutive-day streak is a dense-date island where the ordered attribute is the login date and the aggregate is COUNT(*); the "longest streak" is the max per user and the "current streak" is the island whose end date is today. Once you say "streaks = date islands + per-user max," the streak interview surface reduces to the standard Tabibitosan template plus one extra ranking step.

Visual diagram of consecutive-day login streaks — left an input table of user login dates, a purple arrow labelled 'group by island_id', right a longest-streak and current-streak card per user; a small annotation about gap tolerance and holiday handling; on a light PipeCode card.

The two streak questions.

  • Longest streak per user. Given all-time logins, what's the longest consecutive-day run each user has ever had? Reported on user profiles ("your longest streak: 47 days").
  • Current streak per user. Given today's date, is the user still on a streak, and if so, how long is it? Powered by the daily-open notification that says "keep your 47-day streak alive."

The three-step recipe.

-- Step 1 — dense-date island id
island_id = login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) * INTERVAL '1 day'

-- Step 2 — per-island aggregates
GROUP BY user_id, island_id
  → island_start = MIN(login_date), island_end = MAX(login_date), length = COUNT(*)

-- Step 3 — pick the longest or the current
longest → ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY length DESC) = 1
current → WHERE island_end = CURRENT_DATE
Enter fullscreen mode Exit fullscreen mode

Gap tolerance — the "one-day miss" allowed variant.

  • Sometimes a product allows a 1-day miss without breaking the streak — "you can miss one day per week." The recipe extends: 
-- Flag "gap > tolerance" instead of "gap > 1"
CASE
  WHEN login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) > INTERVAL '2 days'
    OR LAG(login_date) IS NULL
    THEN 1
  ELSE 0
END AS new_streak
Enter fullscreen mode Exit fullscreen mode
  • Then cumulative-sum the flag to get an "extended island id," and aggregate as before. The threshold is the max allowed gap; 2 days tolerates a 1-day miss.

Holiday tolerance — the calendar-join variant.

  • Pre-join a business-calendar table with is_holiday and is_active_day flags. Filter login candidates to only active days before applying the island trick. Or use the calendar's active_day_num as the ordered attribute (as in Section 2's business-day example).
  • For "streak counts if the user is active on every non-holiday" — join the calendar into logins and check that every active day is present.

Current streak — the "streak is still alive today" check.

  • After computing per-island aggregates, filter to island_end IN (CURRENT_DATE, CURRENT_DATE - INTERVAL '1 day').
  • Including yesterday accounts for timezone edge cases — some users' "today" is still yesterday in UTC.
  • The user has an active streak if the filtered row exists; the streak length is the island's length.

Streak-end timestamp for retention analysis.

  • Growth teams want to know when users' streaks ended, not just their length. island_end is exactly this.
  • For churn cohort analysis, group users by island_end week: "how many users ended a streak in week N?" is a leading indicator of churn.
  • For "days since last streak," subtract island_end from CURRENT_DATE. Common in dashboards.

Common sql streaks interview probes.

  • "What's the difference between streak and session?" — streaks are typically per-day granularity; sessions are per-minute. Same island-id trick, different ordered attribute.
  • "How do you tolerate a 1-day miss?" — extend the boundary threshold to 2 days (or use the flag-and-sum flavour with gap > tolerance).
  • "How do you handle holidays?" — pre-join a business calendar; use working_day_num as the ordered attribute instead of raw dates.
  • "Current streak vs longest streak — same query?" — same island-id primitive; different filter/rank at the end. Current filters to island_end >= CURRENT_DATE - 1; longest picks the max per user.
  • "Streaks across multiple activity types?" — union the activities into a single "was_active_today" fact table, then apply the streak recipe.

Worked example — longest streak per user

Detailed explanation. The canonical LeetCode-style ask: given a logins(user_id, login_date) table with distinct rows per (user_id, login_date), return each user's longest consecutive-day streak with start, end, and length.

Question. Given logins(user_id, login_date) distinct per (user, date), write a query that returns (user_id, longest_streak_start, longest_streak_end, longest_streak_length) per user.

Input.

user_id login_date
u1 2026-07-01
u1 2026-07-02
u1 2026-07-03
u1 2026-07-06
u1 2026-07-07
u2 2026-07-05
u2 2026-07-06
u2 2026-07-07
u2 2026-07-08
u2 2026-07-09

Code.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM logins
),
islanded AS (
  SELECT
    user_id,
    login_date,
    login_date - (rn || ' days')::interval AS island_id
  FROM numbered
),
per_island AS (
  SELECT
    user_id,
    island_id,
    MIN(login_date)::date AS streak_start,
    MAX(login_date)::date AS streak_end,
    COUNT(*) AS streak_length
  FROM islanded
  GROUP BY user_id, island_id
),
ranked AS (
  SELECT
    user_id,
    streak_start,
    streak_end,
    streak_length,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_length DESC, streak_end DESC
    ) AS rn
  FROM per_island
)
SELECT
  user_id,
  streak_start AS longest_streak_start,
  streak_end AS longest_streak_end,
  streak_length AS longest_streak_length
FROM ranked
WHERE rn = 1
ORDER BY user_id;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. numbered CTE — per user, order by login_date and assign rn. Same primitive as Section 2.
  2. islanded CTE — compute island_id = login_date - rn days. Consecutive logins share the same island_id.
  3. per_island CTE — group by (user_id, island_id) and aggregate to streak start/end/length.
  4. ranked CTE — per user, rank the islands by length descending, tie-break on end date. ROW_NUMBER() = 1 picks the longest streak.
  5. Final SELECT — filter to the winners and rename for clarity. Output is one row per user.

Output.

user_id longest_streak_start longest_streak_end longest_streak_length
u1 2026-07-01 2026-07-03 3
u2 2026-07-05 2026-07-09 5

Rule of thumb. Longest streak = island-id trick + ROW_NUMBER() = 1 per user. Four CTEs, one output row per user. This is the answer any LeetCode "consecutive numbers" streak problem wants.

Worked example — current streak per user

Detailed explanation. The "streak is still alive today" variant: filter the per-island aggregates to those whose island_end is today or yesterday (accounting for timezone). Users without such an island have no current streak.

Question. Given the same logins table and today's date, return every user's current streak (start, end, length). Users with no current streak should not appear in the output.

Input.

user_id login_date
u1 2026-07-08
u1 2026-07-09
u1 2026-07-10
u2 2026-07-05
u2 2026-07-06
u3 2026-07-10

Assume today is 2026-07-10.

Code.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM logins
),
islanded AS (
  SELECT
    user_id,
    login_date,
    login_date - (rn || ' days')::interval AS island_id
  FROM numbered
),
per_island AS (
  SELECT
    user_id,
    island_id,
    MIN(login_date)::date AS streak_start,
    MAX(login_date)::date AS streak_end,
    COUNT(*) AS streak_length
  FROM islanded
  GROUP BY user_id, island_id
)
SELECT
  user_id,
  streak_start AS current_streak_start,
  streak_end AS current_streak_end,
  streak_length AS current_streak_length
FROM per_island
WHERE streak_end >= CURRENT_DATE - INTERVAL '1 day'
ORDER BY user_id;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. First three CTEs are identical to the "longest streak" query — compute per-island aggregates.
  2. The filter WHERE streak_end >= CURRENT_DATE - INTERVAL '1 day' picks islands that ended today or yesterday. Yesterday accounts for timezone edge cases where a user's "today" may still be yesterday in UTC.
  3. Users who haven't logged in recently have no matching island — they drop out of the output.
  4. If a user has multiple islands ending in the window (unusual, but possible if the data is weird), the query returns both. In practice, a user has at most one current streak; add a ROW_NUMBER = 1 ranking step if you want to be defensive.
  5. For "current streak = 1 day" (user logged in only today), the same query returns a length-1 island. That's usually the right answer for streak-based retention.

Output.

user_id current_streak_start current_streak_end current_streak_length
u1 2026-07-08 2026-07-10 3
u3 2026-07-10 2026-07-10 1

Rule of thumb. Current streak = same island primitive + filter to island_end >= CURRENT_DATE - 1. No extra ranking needed — the filter alone selects the user's active streak.

Worked example — streak with 1-day-miss tolerance

Detailed explanation. A common product ask: "let users miss one day without breaking the streak." The recipe swaps the dense-date subtraction for the flag-and-sum flavour with a 2-day boundary.

Question. Given logins(user_id, login_date), allow up to a 1-day miss between consecutive logins and return the longest tolerant-streak per user.

Input.

user_id login_date
u1 2026-07-01
u1 2026-07-02
u1 2026-07-04
u1 2026-07-05
u1 2026-07-09

Code.

WITH flagged AS (
  SELECT
    user_id,
    login_date,
    CASE
      WHEN login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) > INTERVAL '2 days'
        OR LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) IS NULL
        THEN 1
      ELSE 0
    END AS new_streak
  FROM logins
),
streaked AS (
  SELECT
    user_id,
    login_date,
    SUM(new_streak) OVER (PARTITION BY user_id ORDER BY login_date) AS streak_id
  FROM flagged
),
per_streak AS (
  SELECT
    user_id,
    streak_id,
    MIN(login_date)::date AS streak_start,
    MAX(login_date)::date AS streak_end,
    COUNT(*) AS login_count,
    (MAX(login_date) - MIN(login_date))::int + 1 AS calendar_span
  FROM streaked
  GROUP BY user_id, streak_id
),
ranked AS (
  SELECT
    user_id,
    streak_start,
    streak_end,
    login_count,
    calendar_span,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY calendar_span DESC
    ) AS rn
  FROM per_streak
)
SELECT
  user_id,
  streak_start,
  streak_end,
  login_count,
  calendar_span
FROM ranked
WHERE rn = 1
ORDER BY user_id;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Because we're allowing 1-day misses, the dense-date subtraction trick no longer works (a 1-day miss changes the date - rn value). Switch to the flag-and-sum flavour.
  2. flagged CTE — flag new_streak = 1 when the gap to the previous login exceeds 2 days. A 1-day gap (miss one day) stays inside the streak.
  3. streaked CTE — cumulative-sum the flag per user to produce the tolerant streak_id.
  4. per_streak CTE — aggregate to login_count (number of actual logins in the streak) and calendar_span (span from first to last day, inclusive). The two can differ — a tolerant streak of 10 calendar days may only have 8 logins.
  5. ranked CTE — pick the streak with the longest calendar_span per user. If you'd rather rank by login_count, swap the ORDER BY.

Output.

user_id streak_start streak_end login_count calendar_span
u1 2026-07-01 2026-07-05 4 5

Rule of thumb. Tolerant streaks use the flag-and-sum flavour with a gap threshold. Reporting both login_count and calendar_span gives product a choice of streak definition without re-running the pipeline.

Senior interview question on streak analytics at scale

A senior interviewer might ask: "You run a language-learning app with 50M users and 10B daily-activity rows. Design a nightly pipeline that publishes each user's (longest_streak, current_streak, days_since_last_streak_end) so it's ready for the daily push notification. Where do the joins happen and what does the plan cost?"

Solution Using a two-stage nightly pipeline with materialised streaks 

-- STAGE 1 (nightly) — materialise per-user streaks table
CREATE TABLE user_streaks AS
WITH numbered AS (
  SELECT
    user_id,
    activity_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS rn
  FROM daily_activity
),
islanded AS (
  SELECT
    user_id,
    activity_date,
    activity_date - (rn || ' days')::interval AS island_id
  FROM numbered
),
per_island AS (
  SELECT
    user_id,
    island_id,
    MIN(activity_date)::date AS streak_start,
    MAX(activity_date)::date AS streak_end,
    COUNT(*) AS streak_length
  FROM islanded
  GROUP BY user_id, island_id
)
SELECT * FROM per_island;

-- STAGE 2 (each morning) — build the push-notification-ready table
CREATE TABLE user_streak_summary AS
WITH by_user AS (
  SELECT
    user_id,
    MAX(streak_length) AS longest_streak,
    MAX(CASE WHEN streak_end >= CURRENT_DATE - INTERVAL '1 day' THEN streak_length END) AS current_streak,
    MAX(streak_end) AS last_streak_end
  FROM user_streaks
  GROUP BY user_id
)
SELECT
  user_id,
  longest_streak,
  COALESCE(current_streak, 0) AS current_streak,
  (CURRENT_DATE - last_streak_end)::int AS days_since_last_streak_end
FROM by_user;
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Stage What runs Approx cost on 10B rows
1 Compute per-user islands (window scan + GROUP BY) ~90% of pipeline cost
2 Aggregate to per-user summary ~5% of pipeline cost
2b Join summary to push service via user_id ~5% for the export step

Stage 1 is where the heavy lifting happens; it's a nightly job and gets 30 minutes to run on a warehouse cluster. Stage 2 is cheap and can run in seconds after Stage 1 lands. The push service reads Stage 2 by user_id at delivery time.

Output:

user_id longest_streak current_streak days_since_last_streak_end
u1 47 3 0
u2 12 0 5
u3 100 0 30

Why this works — concept by concept:

  • Split the pipeline into materialise + summarise — the expensive island detection runs nightly and lands in a table; the cheap per-user aggregation runs each morning and lands in the push-service-ready table. This split avoids re-computing islands every morning.
  • Idempotent materialisation — the user_streaks table can be rebuilt on any night without breaking downstream consumers; the island ids are deterministic given the input data. If the pipeline fails, you re-run and get the same output.
  • COALESCE for the "no current streak" case — the MAX(CASE WHEN ...) returns NULL for users without a current streak; COALESCE(current_streak, 0) fills in zero. The push notification templates check current_streak > 0 to decide whether to send.
  • days_since_last_streak_end for retention — the delta from today to the user's last streak end is a leading indicator of churn. Growth teams filter to days_since_last_streak_end BETWEEN 3 AND 7 and target re-engagement campaigns.
  • Cost — Stage 1 is O(N log N) per user for the window sort. On 10B rows with 50M users, this is ~1B operations per partition summed over partitions — 15-30 minutes on a well-clustered Snowflake medium warehouse. Stage 2 is O(unique users) — seconds. Overall, the pipeline hits the daily push cutoff comfortably.

SQL
Topic — window functions (hard)
Window function problems (SQL, hard)

Practice →

SQL Topic — sliding window Sliding window drills

Practice →

5. Run-length encoding + dialect matrix

sql run length encoding compresses a state series into runs — the SUM(CASE WHEN prev <> curr) OVER primitive, plus the MATCH_RECOGNIZE alternative for Snowflake and Oracle

The mental model in one line: RLE compresses a sequence of state values into one row per contiguous run using the same LAG + SUM-of-change-flag primitive as sessionization; when the warehouse ships MATCH_RECOGNIZE (Snowflake, Oracle), you get a declarative alternative that reads like a regex over rows. Once you say "RLE = flag change points + cumulative sum, or MATCH_RECOGNIZE PATTERN," the RLE interview surface reduces to a dialect lookup.

Visual diagram of SQL run-length encoding and the MATCH_RECOGNIZE dialect matrix — top an RLE example with a status series and the compressed run output, bottom a 4-column dialect card comparing Postgres, Snowflake, BigQuery, and SQL Server support for MATCH_RECOGNIZE; on a light PipeCode card.

The RLE primitive.

-- Given (t, status) ordered by t, compress consecutive same-status rows into runs
WITH flagged AS (
  SELECT
    t,
    status,
    CASE
      WHEN status <> LAG(status) OVER (ORDER BY t)
        OR LAG(status) OVER (ORDER BY t) IS NULL
        THEN 1
      ELSE 0
    END AS is_new_run
  FROM series
),
grouped AS (
  SELECT
    t,
    status,
    SUM(is_new_run) OVER (ORDER BY t) AS run_id
  FROM flagged
)
SELECT
  status,
  MIN(t) AS run_start,
  MAX(t) AS run_end,
  COUNT(*) AS run_length
FROM grouped
GROUP BY status, run_id
ORDER BY run_start;
Enter fullscreen mode Exit fullscreen mode

Three CTEs, one output row per run. This is the portable answer that works on every warehouse.

Where RLE lands in pipelines.

  • Status series compression. Sensor readings, service uptime, feature-flag states — sequences with many consecutive same-value rows. Storage cost drops 10-100×.
  • Price plateaus. Slow-moving prices (real-estate listings, insurance quotes) compress dramatically. Financial modelling often carries prices as runs.
  • Feature-flag audit logs. "This flag was on for user X from t1 to t2, then off from t2 to t3." Downstream A/B analysis reads this shape.
  • Slowly changing dimensions (SCD-2). Type-2 slowly-changing dimensions store one row per contiguous state — that's RLE on the SCD-2 output.
  • CDC compaction. Debezium and Fivetran ship one row per change event; downstream compaction collapses consecutive unchanged rows into RLE runs.

MATCH_RECOGNIZE — the declarative alternative.

-- Snowflake / Oracle syntax
SELECT * FROM series
MATCH_RECOGNIZE (
  ORDER BY t
  MEASURES
    FIRST(t) AS run_start,
    LAST(t) AS run_end,
    COUNT(*) AS run_length,
    A.status AS status
  ONE ROW PER MATCH
  PATTERN (A B*)
  DEFINE
    A AS TRUE,
    B AS B.status = A.status
);
Enter fullscreen mode Exit fullscreen mode
  • Reads like a regex over the row sequence: match one row A, then zero or more rows B where B.status = A.status. Each match emits one output row.
  • MEASURES block declares the per-match columns.
  • ONE ROW PER MATCH — one row per run. ALL ROWS PER MATCH would preserve the raw rows.

MATCH_RECOGNIZE dialect matrix.

Warehouse MATCH_RECOGNIZE support
Oracle 12c+ YES — full ANSI
Snowflake YES — full ANSI (since 2021)
Postgres NO (as of 2026) — use LAG + SUM(new_group) OVER
BigQuery NO — use LAG + SUM
SQL Server NO — use LAG + SUM
MySQL 8 NO — use LAG + SUM
Redshift NO — use LAG + SUM

Choosing between the two.

  • Portable code. Use LAG + SUM OVER. Works on every warehouse; readable; easy to reason about.
  • Complex multi-row patterns. Use MATCH_RECOGNIZE. Patterns like "A then B+ then C" (one A, one or more B, one C) are trivial in MATCH_RECOGNIZE and painful in window functions.
  • Regex-like row patterns. Use MATCH_RECOGNIZE. Financial fraud detection, sequence pattern mining, click-stream funnel matching are the classic use cases.
  • Simple RLE. Either works. Prefer LAG + SUM for portability.

Postgres-specific extension: RANGE INCLUSIVE.

  • Postgres ships a range-type extension that can store (status, tsrange) directly. range_agg(tsrange(t, t + INTERVAL '1 day')) collapses consecutive same-status rows into a range set.
  • Concise for storage but non-portable — only Postgres and a few others support ranges.

Snowflake-specific extension: AUTOMATIC_CLUSTERING on the RLE output.

  • Materialise the RLE output as a table and cluster on the state column. Queries like "get all runs where status = 'error' in the last month" become micro-partition-pruned.
  • Common in reliability dashboards where 99% of runs are ok and 1% are error.

Common sql run length encoding interview probes.

  • "Explain the SUM(CASE WHEN prev <> curr) trick." — LAG produces the previous value; CASE flags the change point with a 1; SUM OVER accumulates the flags into a run id.
  • "Handle the first row where LAG is NULL." — extra branch in the CASE: OR LAG(...) IS NULL THEN 1. The first row always starts a run.
  • "Which warehouses support MATCH_RECOGNIZE?" — Oracle 12c+, Snowflake. Not Postgres, BigQuery, SQL Server, MySQL, Redshift.
  • "What's the compression ratio?" — depends on the value stability. A series where 90% of rows are duplicates compresses ~10×. Slowly-changing prices compress 50-100×.
  • "How do you handle nulls in the state column?" — treat NULL as its own value (IS NOT DISTINCT FROM comparison) or exclude NULLs entirely. Every downstream consumer's answer differs.

Worked example — feature-flag rollout compression

Detailed explanation. A feature-flag audit table logs one row per second per user showing the flag state. Most seconds have the same state as the previous second. RLE compresses to one row per (user, state, interval), cutting storage by 10-100×.

Question. Given flag_log(user_id, log_time, flag_state) with per-second rows, compress to one row per (user_id, flag_state, run) with run start / end / length.

Input.

user_id log_time flag_state
u1 10:00:01 off
u1 10:00:02 off
u1 10:00:03 on
u1 10:00:04 on
u1 10:00:05 on
u1 10:00:06 off

Code.

WITH flagged AS (
  SELECT
    user_id,
    log_time,
    flag_state,
    CASE
      WHEN flag_state <> LAG(flag_state) OVER (PARTITION BY user_id ORDER BY log_time)
        OR LAG(flag_state) OVER (PARTITION BY user_id ORDER BY log_time) IS NULL
        THEN 1
      ELSE 0
    END AS is_new_run
  FROM flag_log
),
grouped AS (
  SELECT
    user_id,
    log_time,
    flag_state,
    SUM(is_new_run) OVER (PARTITION BY user_id ORDER BY log_time) AS run_id
  FROM flagged
)
SELECT
  user_id,
  flag_state,
  MIN(log_time) AS run_start,
  MAX(log_time) AS run_end,
  COUNT(*) AS run_length_seconds
FROM grouped
GROUP BY user_id, flag_state, run_id
ORDER BY user_id, run_start;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. flagged CTE — flag is_new_run = 1 when the current flag_state differs from the previous row's, or when there's no previous row (first row per user). The PARTITION BY user_id scoped LAG ensures per-user comparison.
  2. grouped CTE — cumulative-sum is_new_run per user to produce run_id. Every 1 increments the id; every 0 preserves it.
  3. Outer SELECT — group by (user_id, flag_state, run_id). Including flag_state in the group is defensive; the run_id already implies the state, but including it keeps the query readable and avoids surprises if the CASE ever fires incorrectly.
  4. Aggregations — MIN(log_time) is the run start, MAX(log_time) the end, COUNT(*) the run length in seconds. For minute-granularity, divide COUNT(*) by 60.
  5. Compression ratio — the input has one row per second per user; the output has one row per state change per user. If the average state duration is 10 minutes, compression is ~600× per user.

Output.

user_id flag_state run_start run_end run_length_seconds
u1 off 10:00:01 10:00:02 2
u1 on 10:00:03 10:00:05 3
u1 off 10:00:06 10:00:06 1

Rule of thumb. Every slowly-changing state series is a RLE candidate. Compression ratio equals the average run length; storage cost drops proportionally. Materialise the compressed shape as a downstream table for cheap analytical reads.

Worked example — MATCH_RECOGNIZE on Snowflake

Detailed explanation. Snowflake ships full ANSI MATCH_RECOGNIZE. The RLE query above compresses to a shorter, more readable form using PATTERN and DEFINE clauses. This is the Snowflake-native answer to the same question.

Question. On Snowflake, write the same feature-flag RLE query using MATCH_RECOGNIZE.

Input. (Same as above.)

Code.

SELECT
  user_id,
  flag_state,
  run_start,
  run_end,
  run_length
FROM flag_log
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY log_time
  MEASURES
    FIRST(log_time)  AS run_start,
    LAST(log_time)   AS run_end,
    A.flag_state     AS flag_state,
    COUNT(*)         AS run_length
  ONE ROW PER MATCH
  PATTERN (A B*)
  DEFINE
    A AS TRUE,
    B AS B.flag_state = A.flag_state
);
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. PARTITION BY user_id — sessionize each user independently, same as the window functions do.
  2. ORDER BY log_time — establish the row order to match against.
  3. PATTERN (A B*) — match one A row, then zero or more B rows. Every run starts with an A and continues with Bs.
  4. DEFINE A AS TRUE, B AS B.flag_state = A.flag_state — A matches every row unconditionally; B matches the next row only if its flag_state equals A.flag_state — i.e., the run continues while the state is unchanged.
  5. MEASURES — declare the columns for each match. FIRST(log_time) is the run start; LAST(log_time) the end; A.flag_state the state; COUNT(*) the length.

Output. (Same as the LAG + SUM query.)

Rule of thumb. MATCH_RECOGNIZE is the Snowflake-native RLE syntax; the LAG + SUM version is portable. For a Snowflake-only codebase, MATCH_RECOGNIZE reads more clearly. For a multi-warehouse dbt project, stick with LAG + SUM.

Worked example — price plateau compression across dialects

Detailed explanation. A price series with slow changes compresses dramatically under RLE. This example writes the same query in the two flavours (portable and Snowflake) so a candidate can compare readability.

Question. Given prices(symbol, ts, price) with per-minute rows and mostly-stable prices, compress to one row per (symbol, price, run) with run start / end / length. Show both the portable and Snowflake versions.

Input.

symbol ts price
AAPL 09:30 190
AAPL 09:31 190
AAPL 09:32 191
AAPL 09:33 191
AAPL 09:34 191
AAPL 09:35 190

Code.

-- Portable — LAG + SUM (works on every warehouse)
WITH flagged AS (
  SELECT
    symbol,
    ts,
    price,
    CASE
      WHEN price <> LAG(price) OVER (PARTITION BY symbol ORDER BY ts)
        OR LAG(price) OVER (PARTITION BY symbol ORDER BY ts) IS NULL
        THEN 1
      ELSE 0
    END AS is_new_run
  FROM prices
),
grouped AS (
  SELECT
    symbol,
    ts,
    price,
    SUM(is_new_run) OVER (PARTITION BY symbol ORDER BY ts) AS run_id
  FROM flagged
)
SELECT
  symbol,
  price,
  MIN(ts) AS run_start,
  MAX(ts) AS run_end,
  COUNT(*) AS run_length_minutes
FROM grouped
GROUP BY symbol, price, run_id
ORDER BY symbol, run_start;

-- Snowflake — MATCH_RECOGNIZE (shorter, less portable)
SELECT
  symbol,
  price,
  run_start,
  run_end,
  run_length_minutes
FROM prices
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY ts
  MEASURES
    FIRST(ts) AS run_start,
    LAST(ts)  AS run_end,
    A.price   AS price,
    COUNT(*)  AS run_length_minutes
  ONE ROW PER MATCH
  PATTERN (A B*)
  DEFINE
    A AS TRUE,
    B AS B.price = A.price
);
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Both queries produce the same output. The portable version reads any warehouse; the MATCH_RECOGNIZE version reads Snowflake or Oracle only.
  2. The portable version has three CTEs and one GROUP BY. The MATCH_RECOGNIZE version has one query — shorter but requires familiarity with PATTERN syntax.
  3. Plan quality — on Snowflake, both compile to similar physical plans (a window scan or a MATCH_RECOGNIZE-specific operator). MATCH_RECOGNIZE has a slight edge for complex multi-row patterns; for simple RLE, the plans are indistinguishable.
  4. When to prefer MATCH_RECOGNIZE: patterns like "buy signal followed by three consecutive down-days followed by a stop-loss trigger" — regex-like. When to prefer LAG + SUM: simple RLE, sessionization, streaks — the classic patterns.
  5. Team fluency — MATCH_RECOGNIZE requires training. If your team knows window functions cold and MATCH_RECOGNIZE not at all, choose the portable version. Every hour of training is a real cost.

Output.

symbol price run_start run_end run_length_minutes
AAPL 190 09:30 09:31 2
AAPL 191 09:32 09:34 3
AAPL 190 09:35 09:35 1

Rule of thumb. Write portable LAG + SUM for cross-warehouse code. Reach for MATCH_RECOGNIZE only on Snowflake or Oracle when the pattern is genuinely multi-row and regex-like.

Senior interview question on dialect-portable RLE

A senior interviewer might ask: "Your team maintains an analytics warehouse on Postgres and a data-science warehouse on Snowflake. Design the SCD-2 compaction pipeline that runs identically on both and produces the same (user_id, state, valid_from, valid_to) history — walk me through the code and why you'd not use MATCH_RECOGNIZE even though Snowflake supports it."

Solution Using a portable LAG + SUM SCD-2 compactor 

-- SCD-2 compactor — portable Postgres + Snowflake + BigQuery + SQL Server + MySQL
CREATE OR REPLACE VIEW user_state_scd2 AS
WITH flagged AS (
  SELECT
    user_id,
    log_time,
    state,
    CASE
      WHEN state <> LAG(state) OVER (PARTITION BY user_id ORDER BY log_time)
        OR LAG(state) OVER (PARTITION BY user_id ORDER BY log_time) IS NULL
        THEN 1
      ELSE 0
    END AS is_new_run
  FROM user_state_log
),
grouped AS (
  SELECT
    user_id,
    log_time,
    state,
    SUM(is_new_run) OVER (PARTITION BY user_id ORDER BY log_time) AS run_id
  FROM flagged
)
SELECT
  user_id,
  state,
  MIN(log_time) AS valid_from,
  MAX(log_time) AS valid_to,
  COUNT(*) AS event_count,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY MIN(log_time)) AS scd_version
FROM grouped
GROUP BY user_id, state, run_id;
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Step What runs Purpose
1 flagged CTE (LAG + CASE) Flag change points as 1, else 0
2 grouped CTE (SUM OVER) Cumulative-sum to produce run_id per user
3 Aggregate to valid_from / valid_to One row per contiguous state
4 ROW_NUMBER() per user Assign scd_version for change-tracking

The four steps produce a SCD-2-shaped history: one row per contiguous state per user, each row has valid_from, valid_to, and a monotonically-increasing scd_version.

Output:

user_id state valid_from valid_to event_count scd_version
u1 trial 2026-06-01 2026-06-14 14 1
u1 paid 2026-06-15 2026-06-30 16 2
u1 churned 2026-07-01 2026-07-10 10 3

Why this works — concept by concept:

  • Portable primitive — LAG, CASE, SUM OVER, and ROW_NUMBER are ANSI-standard window functions. Every warehouse from Postgres 8.4 onwards ships them. The exact same view definition compiles on Postgres, Snowflake, BigQuery, SQL Server 2012+, MySQL 8, and Redshift.
  • No MATCH_RECOGNIZE even where supported — even though Snowflake supports MATCH_RECOGNIZE, using it would fork the code. The maintenance cost of two parallel implementations vastly exceeds the readability improvement.
  • scd_version via ROW_NUMBER — a monotonically-increasing per-user version number that downstream consumers use as a stable join key. Every dimensional model wants this column; not having it is an interview red flag.
  • valid_to is inclusive — the last state's valid_to is MAX(log_time), not NULL or 9999-12-31. Downstream consumers doing "what's the current state?" filter by WHERE CURRENT_TIMESTAMP BETWEEN valid_from AND valid_to. Adjust to your team's convention.
  • Cost — three window scans + one aggregate. On a 1B-row log, the pipeline runs in single-digit minutes on a modest cluster. Materialise as a table if downstream reads are frequent; keep as a view if you re-run on every batch. Both are valid; the choice depends on the read/refresh ratio.

SQL
Topic — window functions (SQL)
Window functions in SQL — full library

Practice →

SQL
Topic — SQL
SQL problem library — 450+ DE-focused questions

Practice →

Cheat sheet — Gaps & Islands recipe list

  • Universal template. Order rows within each partition. Assign an island id (Flavour A: dense subtraction; Flavour B: cumulative sum of change flag). Group by island id and aggregate. Every gaps-and-islands answer uses this exact three-step pattern.
  • Dense-date island id (Flavour A). login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) * INTERVAL '1 day'. Constant inside an island; changes across islands. Tabibitosan trick.
  • Change-flag island id (Flavour B). SUM(CASE WHEN gap > threshold THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY ts). Increments at every boundary crossing. Works for sessionization, RLE, uptime.
  • Sessionization primitive. LAG(event_time) + CASE WHEN gap > 30 min THEN 1 ELSE 0 END + SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time). Threshold defaults to 30 min per GA / Segment convention.
  • Longest streak per user. Compute island_id, aggregate to per-island (start, end, length), rank per user by length desc, filter to ROW_NUMBER() = 1. Four CTEs; one row per user.
  • Current streak per user. Same island primitive plus filter WHERE island_end >= CURRENT_DATE - INTERVAL '1 day'. Includes yesterday for timezone tolerance.
  • Gap-tolerant streak. Switch to Flavour B with a boundary threshold > 1 day. Threshold of 2 days tolerates a 1-day miss.
  • Business-day streak. Pre-join a business_days(date, business_day_num) calendar table; use business_day_num as the ordered attribute so weekends and holidays don't break the streak.
  • RLE compression. LAG(state) + CASE WHEN state <> LAG(state) THEN 1 ELSE 0 END + SUM(is_new_run) OVER. Three CTEs, one aggregate. Compression ratio equals the average run length.
  • MATCH_RECOGNIZE syntax. MATCH_RECOGNIZE (PARTITION BY ... ORDER BY ... MEASURES FIRST(...), LAST(...), COUNT(*) ... ONE ROW PER MATCH PATTERN (A B*) DEFINE A AS TRUE, B AS B.state = A.state). Snowflake + Oracle only.
  • MATCH_RECOGNIZE dialect matrix. Oracle 12c+ YES · Snowflake YES · Postgres NO · BigQuery NO · SQL Server NO · MySQL NO · Redshift NO. Default to portable LAG + SUM on any multi-warehouse project.
  • PARTITION BY user_id is non-negotiable. Every window function in every gaps-and-islands query partitions by the entity — user, session, symbol, device. Skipping the partition mixes entities; the interviewer catches it every time.
  • First-and-last per island. Swap GROUP BY + MIN/MAX for MIN() OVER (PARTITION BY ..., island_id) window aggregates when you want to enrich each row with its island's boundaries without collapsing.
  • Top-1 per group. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY length DESC) = 1 is the portable "top-1 per user" idiom. Snowflake / BigQuery ship QUALIFY ROW_NUMBER() = 1 shorthand; Postgres has DISTINCT ON (user_id) ORDER BY length DESC.
  • FILTER (WHERE ...). Standard SQL clause for conditional aggregation — MIN(page_url) FILTER (WHERE event_name = 'page_view'). Postgres, Snowflake, BigQuery. SQL Server uses MIN(CASE WHEN ... THEN ... END) as the equivalent.
  • LAST_VALUE IGNORE NULLS forward-fill. For "carry the last non-null value forward" — LAST_VALUE(x IGNORE NULLS) OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Postgres 16+, Snowflake, BigQuery, SQL Server 2022+.
  • SCD-2 compactor. RLE view over a CDC / state log with per-entity valid_from / valid_to and a scd_version. Portable across every warehouse.
  • Compression math. For a slowly-changing series, RLE cuts storage proportionally to the average run length. A 90% duplicate series compresses ~10×; slow-changing prices compress 50-100×.
  • When to reach for MATCH_RECOGNIZE. Multi-row patterns with strict ordering ("A then B+ then C"), regex-like row sequences, fraud detection, sequence pattern mining. Rare in day-to-day analytics; common in specialised finance / risk workloads.
  • When to stick with LAG + SUM. Portability, team fluency, simple RLE, sessionization, streaks. The default answer for 90% of gaps-and-islands questions on 90% of warehouses.

Frequently asked questions

What is the gaps and islands problem in SQL?

sql gaps and islands is a family of query patterns that identify runs of consecutive rows sharing some ordering property — for example, consecutive-day login streaks, sessionization of event streams, uptime windows, and price-plateau compression. The canonical answer assigns each row an "island id" and groups by that id: (a) dense-date sequences use login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) to produce a constant per island; (b) computed-boundary sequences use SUM(CASE WHEN gap > threshold THEN 1 ELSE 0 END) OVER (...) to cumulative-sum a change flag. Once the island id is right, GROUP BY user_id, island_id and aggregate — MIN(ts) as start, MAX(ts) as end, COUNT(*) as length. Senior interviewers love this family because it tests window-function fluency without being about ranking, has both a portable answer (LAG + SUM) and a MATCH_RECOGNIZE alternative on Snowflake / Oracle, and generalises across sessionization, streaks, RLE, and SCD-2 with the same three-step template.

How do you sessionize an event stream in SQL?

Sessionization uses the change-flag flavour of the gaps-and-islands template. In three lines: (1) compute the gap to the previous event with LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time); (2) flag new_session = 1 when the gap exceeds a threshold (usually 30 minutes per Google Analytics / Segment convention) or when there's no previous event; (3) cumulative-sum the flag with SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) to produce a per-user session id. Session-level aggregations then group by (user_id, session_id) and pull MIN(event_time) as session_start, MAX(event_time) as session_end, COUNT(*) as event_count, and per-event details via MIN(...) FILTER (WHERE event_name = 'page_view') for the landing page. The primitive extends to explicit boundary events (sign-out closes a session) by adding a LAG(event_name) = 'sign_out' branch to the CASE.

What is the row_number minus date trick?

The row_number - date trick (also known as the Tabibitosan method) turns a dense-date sequence into constant island ids by subtracting the per-partition ROW_NUMBER() from the ordered date attribute. When consecutive dates advance in lockstep with the row_number (2026-07-01 · rn=1, 2026-07-02 · rn=2, 2026-07-03 · rn=3), the subtraction yields the same constant (2026-06-30) inside the island — a natural GROUP BY key. When a gap appears (2026-07-06 · rn=4), the row_number moves ahead of the date and the subtraction produces a different constant (2026-07-02), correctly starting a new island. Every senior data engineer recognises this trick in ten seconds — practice writing it until you can produce the six-line CTE from memory. It's the fastest answer to LeetCode's "consecutive login days" problem and every senior data-engineering interview's dense-date island question.

How do you compute the longest login streak per user?

Longest-streak-per-user is a four-CTE query on top of the Tabibitosan trick: (1) assign per-user ROW_NUMBER() ordered by login_date; (2) compute island_id = login_date - rn * INTERVAL '1 day'; (3) group by (user_id, island_id) to aggregate per-island (streak_start, streak_end, streak_length); (4) rank per user by streak_length DESC, streak_end DESC and filter to ROW_NUMBER() = 1. The output is one row per user with their longest ever streak. Current-streak-per-user swaps the ranking step for a filter WHERE streak_end >= CURRENT_DATE - INTERVAL '1 day' — the -1 day tolerance handles timezones. Gap-tolerant streaks (allow one-day misses) switch to the change-flag flavour with a 2-day boundary threshold. Business-day streaks pre-join a calendar table with a dense business_day_num and use that as the ordered attribute. Every variation is a two-line change from the base recipe.

What is SQL run length encoding and when do you reach for it?

sql run length encoding compresses a sequence of state values into one row per contiguous run — (state, run_start, run_end, run_length) — using the same LAG + change-flag primitive as sessionization but ordered by a general timestamp instead of a session boundary. The canonical query flags change points with CASE WHEN state <> LAG(state) OVER (...) THEN 1 ELSE 0 END, cumulative-sums the flag into run_id, then groups by (state, run_id) to aggregate. Reach for RLE whenever a slowly-changing series has many consecutive same-value rows: feature-flag audit logs (10-100× compression), sensor readings, uptime pings, price plateaus, and SCD-2 slowly-changing dimensions (valid_from / valid_to history). Compression ratio equals the average run length; downstream analytical reads become dramatically cheaper. Snowflake and Oracle ship the declarative MATCH_RECOGNIZE alternative — PATTERN (A B*) DEFINE B AS B.state = A.state — but the portable LAG + SUM version is the safer default for cross-warehouse code.

Which warehouses support MATCH_RECOGNIZE?

MATCH_RECOGNIZE is the ANSI SQL/JSON row-pattern-matching clause that reads like a regex over ordered rows. Support in 2026: Oracle 12c+ (YES, the first ship in 2013), Snowflake (YES, since 2021 GA), Postgres (NO — no planned support as of 2026), BigQuery (NO), SQL Server (NO), MySQL 8 (NO), Redshift (NO). For cross-warehouse projects, default to the portable LAG + SUM(new_group) OVER pattern; it works everywhere and reads well once your team knows window functions cold. Reach for MATCH_RECOGNIZE only on Snowflake or Oracle when the pattern is genuinely multi-row and regex-like — sequences like "A then B+ then C" for fraud detection, click-stream funnel matching, or financial signal recognition. For simple RLE, sessionization, and streaks, the portable window-function recipe compiles to identical physical plans and doesn't fork your codebase across dialects.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every `sql gaps and islands` recipe above ships with hands-on practice rooms where you write the Tabibitosan island-id derivation, wire the 30-minute sessionization pattern, chase the longest-streak-per-user query, compress a state series with RLE, and rehearse the MATCH_RECOGNIZE alternative against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `consecutive rows sql` answer holds up under a senior interviewer's depth probes.

Practice gaps and islands now →
Window function drills →

Top comments (0)