Practice / window functions

Sessionize the Streaks

hard

A product analyst wants to split each user's activity into sessions. Within a user's stream of events (ordered by ts, a timestamp in minutes), a new session begins whenever the gap from the previous event exceeds 30 minutes — strictly greater than 30, so a gap of exactly 30 stays in the same session. The first event for each user always starts session 1.

events

Your task: return user_id, ts, and session — a 1-based session number per user, increasing by one each time a gap over 30 minutes opens a new session.

Assign your answer DataFrame to result. Row order doesn't matter.

eventsinput DataFrame

Schema
columntype
user_idlong
tslong
Sample rows
user_idts
10
130
160
1200
25
2100
Expected output shape
user_idtssession· 6 rows
Hint

Per user ordered by ts, mark a new session when ts - lag(ts) > 30 OR lag(ts) is null (the first event). Cast that flag to int and take a running sum over rowsBetween(unboundedPreceding, currentRow) to turn the flags into 1-based session numbers.

Lesson refresher

This problem builds on Window Functions (~9 min). Pop it open in a new tab if you want a quick recap.

Loading editor…
Hit Run to execute your code and see the output here.