Sessionization: Grouping Events with an Inactivity Timeout

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard36 minstreamingLAGcumulative-sum flagevent-time windowingsessionizationsession_id assignment

Stamp each event with the session it belongs to: LAG the gap, flag a new session past the timeout, running-sum the flag into a session id.

What a session is, and why you build one

Raw event logs have no notion of a "visit". You get a stream of (user_id, event_ts) rows, and the analytics questions all assume sessions: how long is a typical visit, how many events happen per session, where inside a session do people drop off. A session is a run of one user's events with no gap longer than an inactivity timeout, and 30 minutes is the common default. Sessionization is the query that stamps each event with the session it belongs to, and it is one of the most common streaming-flavored questions on a DE screen.

The three-step build

Work on events(user_id, event_ts). The spirit is the same as gaps-and-islands: find where a run breaks, then number the runs.

Look back one event. LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) hands each event the previous event's timestamp for that user, and NULL for the user's first event.
Flag a new session on a long gap. A user's first event and any event more than 30 minutes after the previous one open a fresh session: prev_ts IS NULL OR (julianday(event_ts) - julianday(prev_ts)) * 1440 > 30. julianday returns days, so multiplying the difference by 1440 converts it to minutes.
Running-sum the flag into a session number. SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_ts) counts how many sessions have started up to and including this event, so every event inside one session shares the same number. Concatenate it with the user id for a stable id: user_id || '-' || session_seq.

The demo below shows every intermediate column (the previous timestamp, the gap in minutes, the new-session flag, and the running session number) so you can watch the 80-minute gap flip the flag and bump the number.

Table

The 80-minute gap trips new_session to 1, and the running sum of that flag bumps session_seq from 1 to 2.
event_ts	gap_min	new_session	session_seq
09:00	NULL	1	1
09:10	10	0	1
10:30	80	1	2
10:45	15	0	2

The 80-minute gap trips new_session to 1, and the running sum of that flag bumps session_seq from 1 to 2.

The ambiguity interviewers probe

"Thirty minutes of inactivity" almost always means since the previous event, which is exactly what the LAG gap measures and what we use here. A few teams instead mean 30 minutes since the session started, a fixed-length window. The two disagree: a steady trickle every 20 minutes is one endless session under the first rule and a series of capped windows under the second. State which one you are implementing, because the interviewer is often checking that you noticed the fork.

Interview nuance: this is the batch-SQL form of a streaming session window. It is exact over a bounded table, but a live stream needs a watermark to decide when a session is closed and safe to emit, since a late event could still extend it.

In the warehouse this differs. Flink and Spark Structured Streaming have a native session window (SESSION(event_time, INTERVAL '30' MINUTE)) where a watermark bounds late data, and Snowflake exposes CONDITIONAL_TRUE_EVENT and MATCH_RECOGNIZE. The gap math changes as well (TIMESTAMPDIFF or DATE_DIFF instead of julianday() * 1440). The LAG-plus-running-sum pattern is what you write over a bounded batch table, and it is the answer an interviewer wants.

Sample data for this example

CREATE TABLE events (event_id INTEGER PRIMARY KEY, user_id INTEGER, event_ts TEXT);
INSERT INTO events (event_id, user_id, event_ts) VALUES
  (1, 1, '2026-03-01 09:00:00'),
  (2, 1, '2026-03-01 09:10:00'),
  (3, 1, '2026-03-01 10:30:00'),   -- 80 min gap starts a new session
  (4, 1, '2026-03-01 10:45:00');

Worked example (SQL)

-- Every intermediate column: prev_ts gap, the new-session flag, the running session number.
WITH with_prev AS (
  SELECT event_id, event_ts,
         LAG(event_ts) OVER (ORDER BY event_ts) AS prev_ts
  FROM events
),
flagged AS (
  SELECT event_id, event_ts, prev_ts,
         ROUND((julianday(event_ts) - julianday(prev_ts)) * 1440) AS gap_minutes,
         CASE WHEN prev_ts IS NULL OR (julianday(event_ts) - julianday(prev_ts)) * 1440 > 30
              THEN 1 ELSE 0 END AS new_session
  FROM with_prev
)
SELECT event_id, event_ts, gap_minutes, new_session,
       SUM(new_session) OVER (ORDER BY event_ts) AS session_seq
FROM flagged
ORDER BY event_ts;

Apply

Your turn

The task this lesson builds to.

Write a query that assigns a session_id to each event, starting a new session after 30 minutes of user inactivity, and returns (event_id, user_id, event_ts, session_id) over events(event_id, user_id, event_ts).

Measure the gap to the previous event per user with LAG, flag a new session when that gap exceeds 30 minutes (or the event is the user's first), running-sum the flag per user into a session number, and build session_id as user_id || '-' || session_number. Alias the columns exactly event_id, user_id, event_ts, session_id.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that returns one row per session as (session_id, start_ts, end_ts, event_count, duration_minutes), over the same events(event_id, user_id, event_ts) table with the 30-minute inactivity rule.

Assign sessions as before, then aggregate per session: the earliest event timestamp as start_ts, the latest as end_ts, the number of events, and the whole-minute duration between the first and last event. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.