How do you group event rows into sessions that break after 30 minutes of inactivity?

## Why the arithmetic trick does not apply `value - ROW_NUMBER()` works because "consecutive" means "exactly one step apart" — the arithmetic encodes that rule implicitly. Sessionisation has a different rule: a run continues while the elapsed time is *within* a tolerance, and the timestamps are irregular. You cannot express a tolerance by subtracting a counter, so you state the break condition in SQL and derive the run id from it. ## The two-pass construction ```sql WITH flagged AS ( SELECT user_id, event_time, CASE WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) <= INTERVAL '30' MINUTE THEN 0 ELSE 1 END AS starts_session FROM events ), sessioned AS ( SELECT user_id, event_time, SUM(starts_session) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) AS session_id FROM flagged ) SELECT user_id, session_id, MIN(event_time) AS started_at, MAX(event_time) AS ended_at, COUNT(*) AS event_count FROM sessioned GROUP BY user_id, session_id ORDER BY user_id, started_at; ``` Read it as three ideas. **Flag the breaks**: one boolean per row, computed by looking backwards one row. **Accumulate the flags**: a running total that only moves at a break, so it is constant within a session and strictly increasing across sessions. **Collapse**: ordinary grouping on that id. The two passes must be separate query blocks. A window function cannot be nested inside another window function's argument, so the flag has to be materialised in a CTE or derived table before the running `SUM()` can consume it. ## The first row of every partition `LAG()` has no previous row at the start of a partition, so it returns NULL unless you pass a default. `event_time - NULL` is NULL, `NULL <= INTERVAL '30' MINUTE` evaluates to unknown, and a `CASE` whose `WHEN` is unknown falls through to `ELSE` — yielding `1`. That is exactly right: the first event of a user opens session 1, and the running sum starts at 1 rather than 0. This is one of the few places where three-valued logic quietly does the correct thing, and saying so demonstrates you did not just copy the pattern. ## Frame and ordering details Spell the frame out as `ROWS UNBOUNDED PRECEDING` (shorthand for `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`). With an `ORDER BY` and no frame clause the default frame is range-based over peer rows, which behaves differently when several events share the exact same timestamp. Being explicit makes the running total deterministic and communicates intent. The ordering must be a total order for the result to be reproducible. If two events can carry identical timestamps, add a tiebreaker such as the event id to both `OVER` clauses; otherwise which of the tied rows is treated as the predecessor is arbitrary. ## Generalising the break condition The same skeleton handles any run definition — only the `CASE` changes. - **Value change** (collapse consecutive rows with the same status into one run): flag `1` when `status IS DISTINCT FROM LAG(status) OVER (...)`. `IS DISTINCT FROM` is the standard NULL-safe inequality, which matters because a plain `<>` returns unknown when either side is NULL and would fail to open a new run. Engines differ on spelling — MySQL offers the NULL-safe `<=>` operator — so check your engine. - **Irregular numeric step**: flag when `v - LAG(v) OVER (...) <> :step`. - **Business rule**: any predicate you can write, including one over several columns. That generality is the reason to know this construction even though the difference trick is shorter: one pattern covers sessionisation, status runs, price-change intervals and tolerance-based streaks alike. ## Cost and correctness notes Both passes need the rows ordered by the partition key and the timestamp, so the shape is one ordering reused by both window operators — provided both `OVER` clauses use the same partition and order. Keep them identical, or name the window once with a `WINDOW` clause, so nothing forces a second ordering. A useful validation: the number of sessions per user must equal the number of rows flagged `1` for that user, and the session event counts must sum to the user's event count. If they do not, the partition key is missing from the `GROUP BY` and two users' sessions have been merged — the classic bug, because session ids restart at 1 in every partition and therefore collide across users.