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 DATEDIFF('minute',
             LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts),
             event_ts) > 30
      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.

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. Drop that IS NULL arm and every user's first session either disappears from the output or, worse, fuses with a NULL-comparison behavior that varies by engine, which is the kind of dialect-dependent bug that passes review on one warehouse and breaks on another.

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?", extra OR terms in the boundary CASE; the structure is why this pattern beats hardcoded tricks.

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).