Sessionize a raw event stream in SQL: a gap of more than 30 minutes starts a new session.

TL;DR: Use LAG to get each user's previous event time, flag a boundary when the gap exceeds 30 minutes (or LAG is NULL for the first event), then a running SUM of that flag mints a session id that increments only at boundaries. The first-event NULL is the case interviewers probe.

How to approach it

Recognize it as the generalized gaps-and-islands problem: islands are defined by a gap threshold, not by exact consecutiveness, so the date-minus-row_number trick doesn't apply. The general weapon: LAG to detect boundaries, running SUM to assign session ids. Clarify the gap rule (strictly greater than 30 min? per user? does a session cap exist?) and the desired output grain (event-level session ids vs one row per session).

A strong answer

WITH flagged AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) IS NULL
        OR event_ts > DATEADD('minute', 30,
             LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts))
      THEN 1 ELSE 0
    END AS is_new_session
  FROM events
),
sessions AS (
  SELECT
    user_id,
    event_ts,
    SUM(is_new_session) OVER (
      PARTITION BY user_id
      ORDER BY event_ts
      ROWS UNBOUNDED PRECEDING
    ) AS session_seq
  FROM flagged
)
SELECT
  user_id,
  session_seq,
  MIN(event_ts)               AS session_start,
  MAX(event_ts)               AS session_end,
  COUNT(*)                    AS events,
  DATEDIFF('minute', MIN(event_ts), MAX(event_ts)) AS duration_min
FROM sessions
GROUP BY user_id, session_seq;

Narrate the two moves: (1) LAG compares each event to the previous one for that user, first event or a >30-minute gap flags a boundary; (2) the cumulative SUM of boundary flags turns flags into a monotonically increasing session number, so events between boundaries share an id. For a globally unique session key, concatenate user_id || '-' || session_seq or hash with the session start. Compare timestamps at a stated precision, never through a fractional count: in SQLite, (julianday(ts) - julianday(prev)) * 1440 puts an exact 30-minute gap at 30.0000002 minutes and splits a session that should stay whole, while whole seconds, unixepoch(ts) - unixepoch(prev) > 1800, keep exactly 30 minutes together and split at 30 minutes 1 second (executed in sqlite). Snowflake's DATEDIFF('minute', ...) counts minute boundaries crossed rather than elapsed time, which is why the query above compares against DATEADD instead.

Add the dialect note that lands at Databricks: in Spark SQL this runs as two window functions over the same (user_id, event_ts) ordering, one shuffle, then per-partition sorts, and at streaming scale you'd instead use stateful processing (session_window in Structured Streaming) because SQL-over-history recomputes everything. Knowing where the SQL pattern stops scaling is the senior signal.

Step 3 is the trick worth remembering: a running sum over a boolean is how you convert boundaries into group labels, and it is the same pattern as gaps and islands. Partition by user throughout, because a global order interleaves users and produces sessions that span people.

Five events are enough to watch the machinery run (executed in sqlite). One user's events at 10:00, 10:10, 11:00, 11:05, 11:20 with the 30-minute rule:

Read the last column downward and the trick explains itself: the running sum stays flat across every 0 and steps up exactly at each 1, which is what "mint an id that increments only at boundaries" means mechanically. The first row is the editor-note edge case doing the right thing: LAG returns NULL, the CASE treats NULL as a boundary, and the first event founds session 1 instead of vanishing. What dropping that arm does depends on how the flag is written, which is why it is worth running rather than guessing (both executed in sqlite). In the CASE form above, the NULL comparison falls to ELSE 0, so every user's first session is numbered 0 instead of 1: harmless until something downstream assumes ids start at 1. In the boolean form from the steps table (ts - prev_ts > interval '30 min', summed as an integer), the first event's flag is NULL, so its running sum is NULL, and the first event splits off from the rest of its session as a one-event session with a NULL id. That second failure passes a glance at the output and quietly inflates session counts.

What interviewers probe next

• "Average session duration / sessions per user per day", trivial on top of the final CTE; they're checking your output grain is right.
• "Two events at the exact same timestamp?", same session (gap 0); but note nondeterministic LAG ordering needs a tiebreaker (event_id) for stable results.
• "Late-arriving events?", recompute affected partitions (idempotent overwrite by date) or accept watermark semantics in streaming.
• "What about a 24h session cap, or a new session on campaign change?", a campaign change is another OR term in the boundary CASE, because it compares an event with its neighbour. A cap on total session length is different: it depends on when the session started, which adjacent events cannot see (one event every ten minutes never trips the 30-minute rule and can run for days). It needs a second pass that splits long sessions by elapsed time from their start, or stateful processing.

Common mistakes

• Reaching for date-minus-ROW_NUMBER, which only handles exact +1 gaps.
• Forgetting PARTITION BY user_id, sessions bleed across users.
• Using RANGE instead of ROWS in the running sum (or omitting the frame and tripping on RANGE-default tie behavior).
• Computing duration as last-minus-first without handling single-event sessions (duration 0 is fine; NULL is not).