Event Sessions (30-Minute Gap)

A support dashboard needs one row per user session, including its start, end, and event count. Create a view named result from:

events(event_id INT PRIMARY KEY, user_id INT NOT NULL, event_type TEXT NOT NULL, event_ts TIMESTAMPTZ NOT NULL)

Contract

Return user_id, session_index, session_start, session_end, event_count. Order by user_id, then session_index.

• Process each user independently in ascending (event_ts, event_id) order.
• The first event starts session 1. Each later gap greater than 30 minutes starts another session. An exact 30-minute gap stays in the same session.
• Compare adjacent events, not each event with the session start.
• Keep tied timestamps as separate events. Use event_id to break ties consistently in every window stage.
• Session start/end are the minimum/maximum event time; count every event.
• An empty input produces no rows. Session length itself is not capped.

Worked input and output

For user 7, events with IDs 1 through 5 occur at 09:00, 09:30, 10:00, 10:31, 10:31 on the same UTC date. Output:

Times are abbreviated here; return actual timestamps in the view.

Build it in stages

1. Attach the previous event's timestamp using LAG per user.
2. In another CTE, mark the first event and long gaps with 1; mark others 0.
3. Prefix-sum those markers with a ROWS frame and the same unique ordering.
4. Group by user and the resulting session index.

Inspect each intermediate table before assembling the view. The lesson gives window expressions; reconstruct the complete query yourself. Test a second user, equal timestamps, and a gap just above the threshold before submitting.