Session Windows: The Interview-Speed Sessionization Re-Drill

Level 9: Streaming & Change Data Capturehard18 minsession windowsgaps and islandsLAGrunning SUMper-session aggregationtimeboxing

Rebuild the sessionization query unscaffolded against a 15-minute clock, and map it onto the stream engine's session-window operator.

One drill, two payoffs

Nothing here is new SQL. sql-l5-sessionization already taught the ladder: LAG the previous event time, flag a new session when the gap exceeds the timeout, running-sum the flag into a session number. Go back and read it if the shape is not in your fingers yet, because this lesson does not repeat it.

What is worth saying once, because the research says to say it: this exact pattern is a top-cited SQL interview question in its own right (gaps and islands, on every canonical pattern list) and it is precisely how a stream engine's session-window operator works. One drill pays twice, which is why it earns a slot in a streaming module rather than being left in the SQL section.

The operator mapping, which is the new part

The third window type has no fixed size. A session window is defined by the data: it opens on an event, extends for as long as the gap to the next event stays under an inactivity timeout, and closes when the timeout elapses with nothing arriving. The engine keeps per-key state, which is nothing more than the last event time it saw for that key, and each arriving event either extends the open window or starts a new one.

Table

The session-window operator holds one number per key, the last event time, and every arrival either extends the window or closes it. A gap exactly equal to the timeout extends.
user 501 event	gap from previous	operator state transition
08:30	(first after a long silence)	open a session
08:35	5 min	extend
09:00	25 min	extend
09:30	30 min exactly	extend, because 30 does not exceed 30
10:20	50 min	close the session, open a new one

The session-window operator holds one number per key, the last event time, and every arrival either extends the window or closes it. A gap exactly equal to the timeout extends.

There is one thing the operator can do that a batch query never has to think about: emit. A session cannot be published the moment its last event lands, because a later event could still extend it. The engine may only close and emit a session once the previous lesson's watermark has passed the last event time plus the gap timeout. That makes session windows the one window type that cannot exist without watermarks, and it is the connection between this lesson and the last one.

Common mistake: treating the timeout as inclusive. "A new session starts when the gap exceeds 30 minutes" means strictly greater than 30 minutes; a gap of exactly 30 minutes extends the current session. The seed below contains that case deliberately, and a >= produces a plausible-looking answer with one session too many.

Interview nuance: state which rule you are implementing before you write it. "Thirty minutes of inactivity" almost always means thirty minutes since the previous event, which is what LAG measures, but some teams mean thirty minutes since the session started, which is a fixed-length window and gives different answers on a steady trickle. Saying that out loud in the first thirty seconds is free signal.

The clock

The readiness bar for this pattern is narrating and writing it in 12 to 15 minutes. Set a 15-minute timer before you open the Apply. There are no hints on the Apply or the Practice and no starter skeleton, on purpose: the goal state is the grade. The optional drills below keep their hints.

On a real platform this differs. sql-l5-sessionization already names the native operators and the vendor equivalents, so the one thing to add here is the emit condition: a session may only be published once the watermark has reached last_event_time + gap. That is a concrete inequality rather than a vague "wait a while", and it is why session windows are the one window type that cannot exist without a watermark. The operator call is also version-sensitive: older Flink SQL writes the group-window form SESSION(event_time, INTERVAL '30' MINUTE), while current Flink uses the windowing table-valued function SESSION(TABLE t PARTITION BY user_id, DESCRIPTOR(event_time), INTERVAL '30' MINUTE), which makes the per-key state explicit in the syntax. The LAG-plus-running-sum form you are about to write is the batch answer, it is exact over a bounded table, and it is the one an interviewer is asking for.

Sample data for this example

CREATE TABLE page_view_events (
  user_id       INTEGER,
  event_time_ms INTEGER,  -- epoch milliseconds; 30 minutes is 1800000
  page          TEXT
);
INSERT INTO page_view_events (user_id, event_time_ms, page) VALUES
  (501, 1767250800000,     'home'),
  (501, 1767251040000,   'search'),
  (501, 1767251340000,  'product'),
  (501, 1767251580000,  'product'),
  (501, 1767251880000,     'cart'),
  (501, 1767252360000, 'checkout'),
  (501, 1767256200000,     'home'),
  (501, 1767256500000,   'search'),
  (501, 1767258000000,  'product'),
  (501, 1767259800000,     'cart'),
  (501, 1767262800000,     'home'),
  (502, 1767251100000,     'home'),
  (502, 1767251520000,   'search'),
  (502, 1767252000000,  'product'),
  (502, 1767252780000,  'product'),
  (502, 1767253620000,     'cart'),
  (502, 1767256080000,     'home'),
  (502, 1767256560000,   'search'),
  (502, 1767256980000,  'product'),
  (502, 1767257400000,     'cart'),
  (502, 1767257940000, 'checkout'),
  (502, 1767258900000,     'home'),
  (503, 1767251400000,     'home'),
  (503, 1767252300000,   'search'),
  (503, 1767254100000,  'product'),
  (503, 1767259200000,     'home'),
  (503, 1767259380000,  'product'),
  (503, 1767259680000,     'cart'),
  (503, 1767259920000, 'checkout');

Worked example (SQL)

-- The raw stream, nothing computed. Read the data before you start the timer.
SELECT user_id, event_time_ms, page
FROM page_view_events
ORDER BY user_id, event_time_ms
LIMIT 12;

Apply

Your turn

The task this lesson builds to.

Set a 15-minute timer, then write a query that returns each event with the session it belongs to, as (user_id, event_time_ms, session_number), ordered by user_id then event_time_ms, over page_view_events(user_id, event_time_ms, page).

Sessions are per user. A new session starts on a user's first event and whenever the gap from that user's previous event exceeds 30 minutes (1800000 ms); a gap of exactly 30 minutes stays in the same session. Number each user's sessions from 1 in time order, and alias the columns exactly as named.

1 automated check is waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a query that returns one row per session, as (user_id, session_number, session_start_ms, session_end_ms, events_in_session, duration_minutes), longest duration first and then by user_id and session_number, over the same page_view_events table and the same 30-minute inactivity rule.

session_start_ms and session_end_ms are the first and last event times in the session, events_in_session is how many events it holds, and duration_minutes is the whole minutes between start and end. A single-event session is a real session with a duration of zero. Alias the columns exactly as named.

1 automated check is waiting in the workspace.