Sessionize an Event Stream
For each user, start a session at their first event and whenever the gap from their previous event is strictly greater than 30 minutes. A gap of exactly 30 stays in the session. Times are integer minutes; sort events by (minute, event_id), independently for each user. IDs are unique; equal timestamps still represent separate events.
Return user_id, session_no (starting at 1 per user), start_minute, end_minute, event_count. Order by user_id, session_no.
Schema (all columns are NOT NULL unless explicitly nullable):
CREATE TABLE events (event_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, minute INTEGER NOT NULL);Write one read-only SQLite query. The tables are already populated. Return exactly the requested columns and ordering, with no headers. Empty input produces no rows unless specified otherwise.
Example setup:
INSERT INTO events VALUES (1,1,0),(2,1,30),(3,1,61),(4,2,5);Expected output:
1|1|0|30|2
1|2|61|61|1
2|1|5|5|1Timebox: 9 minutes. Focus on correctness at boundaries and duplicate handling.
Sample tests