Build on E2. Per user, assign each event a session_no (1, 2, 3, …): the user's first session is 1, and every event that starts a new session under E2's rule (gap > 30 minutes) moves the number up by one.
Sort by user_id then event_ts ascending.
Returnevent_iduser_idevent_tssession_no
About this problem
This problem practises the second half of sessionization: turning “does this event start a new session?” into a running session number. Building on the previous step’s rule, an event starts a new session when it is the user’s first or comes more than 30 minutes after their previous event. You mark those starts in sess_events, then keep a running count of them per user_id in event order, so the first session is 1 and each later start moves the number up by one. It shows how LAG and a cumulative SUM work together.
Several small errors produce numbers that look reasonable. A gap of exactly 30 minutes does not start a new session, because the rule is strictly more than 30. In the sample, u_d’s two events are exactly 30 minutes apart and belong to one session; a >= test splits them. Forgetting that the first event is a session start gives numbering that begins at 0. A running SUM over all users carries one user’s count into the next, so later users start part-way through the count. Using DENSE_RANK or ROW_NUMBER instead numbers events, not sessions. The subtlest trap is the default window frame: with only ORDER BY event_ts, events sharing a timestamp are treated as peers and summed together, so tied events share one running total while every untied row looks correct.
Read the lesson: Sessionization & Event Sequences (P24) →