codejump
HardSQL

Sessionize an Event Stream

SQLDatabasesWindow Functions

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|1

Timebox: 9 minutes. Focus on correctness at boundaries and duplicate handling.

Sample tests

Test #1Exact threshold and user isolation
Input: "CREATE TABLE events (event_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, minute INTEGER NOT NULL);\nINSERT INTO events VALUES (1,1,0),(2,1,30),(3,1,61),(4,2,5);"
Output: "1|1|0|30|2\n1|2|61|61|1\n2|1|5|5|1"
Test #2Equal timestamps
Input: "CREATE TABLE events (event_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, minute INTEGER NOT NULL);\nINSERT INTO events VALUES (2,1,10),(1,1,10),(3,1,41);"
Output: "1|1|10|10|2\n1|2|41|41|1"