Gaps, islands & sessionization

row_number differencing, gap-to-session boundaries, session metrics, off-by-one at boundaries.

• ★The islands trick

  Subtracting a per-group row_number from a global one gives a constant per consecutive run — derive it, don't recite it.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE reads(t INT, gate VARCHAR); INSERT INTO reads VALUES (1,'in'),(2,'in'),(3,'out'),(4,'in');

  The trap

  SELECT COUNT(*) AS len FROM reads WHERE gate='in';

  3

  The fix

  SELECT island, COUNT(*) AS len FROM (SELECT t - ROW_NUMBER() OVER (ORDER BY t) AS island FROM reads WHERE gate='in') GROUP BY island ORDER BY island;

  0	2
  1	1

  Counting 'in' rows says one run of 3, but the gate reopened at t=4 after an 'out' — the row_number-difference key splits it into the two real runs.

• Session metrics after grouping

  Duration and events-per-session come from grouping by the derived session id.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE pings(sid INT, ts INT); INSERT INTO pings VALUES (1,0),(1,10),(2,20),(2,25);

  The trap

  SELECT sid, COUNT(*) AS mins FROM pings GROUP BY sid ORDER BY sid;

  1	2
  2	2

  The fix

  SELECT sid, MAX(ts) - MIN(ts) AS mins FROM pings GROUP BY sid ORDER BY sid;

  1	10
  2	5

  Session duration read as the event count (2, 2) instead of the elapsed span — once an id is minted, sessions are a grain, and duration is MAX(ts) - MIN(ts) per session (10 and 5), not the number of pings.

• Off-by-one at boundaries

  The first event of a session and sessions spanning midnight are the classic off-by-one traps (P12 crossover).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE pings(t INT); INSERT INTO pings VALUES (0),(30);

  The trap

  SELECT SUM(CASE WHEN gap >= 30 THEN 1 ELSE 0 END) AS new_sessions FROM (SELECT t - LAG(t) OVER (ORDER BY t) AS gap FROM pings) WHERE gap IS NOT NULL;

  1

  The fix

  SELECT SUM(CASE WHEN gap > 30 THEN 1 ELSE 0 END) AS new_sessions FROM (SELECT t - LAG(t) OVER (ORDER BY t) AS gap FROM pings) WHERE gap IS NOT NULL;

  0

  A gap of exactly 30 minutes started a new session under '>=' but not '>' — the exactly-timeout gap is decided by one character, nobody chose, and downstream session counts shift; the boundary must be stated, not inherited from the operator.

• LAG detects the gap

  LAG gives each row its predecessor — the difference is the gap, and the first row's LAG is NULL by design.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE matches(player VARCHAR, d INT); INSERT INTO matches VALUES ('Ada',1),('Ada',5),('Ada',40);

  The trap

  SELECT player, d FROM (SELECT player, d, LEAD(d) OVER (PARTITION BY player ORDER BY d) - d AS gap FROM matches) WHERE gap > 21 ORDER BY d;

  Ada

  5

  The fix

  SELECT player, d FROM (SELECT player, d, d - LAG(d) OVER (PARTITION BY player ORDER BY d) AS gap FROM matches) WHERE gap > 21 ORDER BY d;

  Ada

  40

  The absence alert flagged the match before the long gap instead of the one after it — LAG measures each row against its predecessor, and the first row's LAG is NULL by design.

• Sessionize by running-SUM of flags

  Sessionization = flag boundaries (gap > timeout) then running-SUM the flags into a session id — and the partition must be per entity or sessions bleed across them.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE obs(volunteer VARCHAR, ts TIMESTAMP); INSERT INTO obs VALUES ('A', TIMESTAMP '2024-05-01 09:00'),('A', TIMESTAMP '2024-05-01 09:40'),('A', TIMESTAMP '2024-05-01 12:30'),('B', TIMESTAMP '2024-05-01 09:10'),('B', TIMESTAMP '2024-05-01 09:30');

  The trap

  WITH flagged AS (SELECT volunteer, ts, CASE WHEN ts - LAG(ts) OVER (ORDER BY ts) > INTERVAL '2 hours' OR LAG(ts) OVER (ORDER BY ts) IS NULL THEN 1 ELSE 0 END AS new_session FROM obs) SELECT volunteer, strftime(ts, '%H:%M') AS mark, SUM(new_session) OVER (ORDER BY ts) AS session_id FROM flagged ORDER BY ts;

  A	09:00	1
  B	09:10	1
  B	09:30	1
  A	09:40	1
  A	12:30	2

  The fix

  WITH flagged AS (SELECT volunteer, ts, CASE WHEN ts - LAG(ts) OVER (PARTITION BY volunteer ORDER BY ts) > INTERVAL '2 hours' OR LAG(ts) OVER (PARTITION BY volunteer ORDER BY ts) IS NULL THEN 1 ELSE 0 END AS new_session FROM obs) SELECT volunteer, strftime(ts, '%H:%M') AS mark, SUM(new_session) OVER (PARTITION BY volunteer ORDER BY ts) AS session_id FROM flagged ORDER BY volunteer, ts;

  A	09:00	1
  A	09:40	1
  A	12:30	2
  B	09:10	1
  B	09:30	1

  Without PARTITION BY, one volunteer's session count ran straight into the next volunteer's rows — sessionization needs both windows partitioned per entity or the sessions bleed across them.

• Merging overlapping ranges

  Merging overlapping ranges is islands-on-intervals — a range starts a new island only if it begins after the running max of all previous ends.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE bk(s INT, e INT); INSERT INTO bk VALUES (1,9),(2,3),(8,10);

  The trap

  WITH p AS (SELECT s, e, LAG(e) OVER (ORDER BY s) AS prev_end FROM bk), g AS (SELECT s, e, SUM(CASE WHEN prev_end IS NULL OR s > prev_end THEN 1 ELSE 0 END) OVER (ORDER BY s) AS island FROM p) SELECT MIN(s) AS start, MAX(e) AS end FROM g GROUP BY island ORDER BY start;

  1	9
  8	10

  The fix

  WITH f AS (SELECT s, e, MAX(e) OVER (ORDER BY s ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max FROM bk), g AS (SELECT s, e, SUM(CASE WHEN prev_max IS NULL OR s > prev_max THEN 1 ELSE 0 END) OVER (ORDER BY s) AS island FROM f) SELECT MIN(s) AS start, MAX(e) AS end FROM g GROUP BY island ORDER BY start;

  1

  10

  The bookings (1,9),(2,3),(8,10) merged into two blocks instead of one — comparing each range to the previous row's end let the contained (2,3) reset the frontier to 3, so (8,10) wrongly started a new island; the running MAX of all previous ends keeps the frontier at 9.