events(event_id, user_id, event_at)

Give every event a session number. A new session starts when the gap from that user's previous event is more than 30 minutes; a gap of exactly 30 minutes stays in the same session. Numbering restarts at 1 for each user.

Return event_id, user_id, event_at and session_number, ordered by user then time then event_id, which is also the tie-break when two events share a timestamp.

The shape is a running total: mark each event that opens a session with a 1, then take the cumulative sum of those marks.

Tables

events

event_id  user_id  event_at
--------  -------  -------------------
1         10       2026-08-01 09:00:00
2         10       2026-08-01 09:10:00
3         10       2026-08-01 09:40:00
4         10       2026-08-01 10:11:00
5         10       2026-08-01 10:15:00
6         11       2026-08-01 09:00:00
7         11       2026-08-01 09:00:00
8         11       2026-08-02 09:00:00

Expected result

event_id  user_id  event_at             session_number
--------  -------  -------------------  --------------
1         10       2026-08-01 09:00:00  1
2         10       2026-08-01 09:10:00  1
3         10       2026-08-01 09:40:00  1
4         10       2026-08-01 10:11:00  2
5         10       2026-08-01 10:15:00  2
6         11       2026-08-01 09:00:00  1
7         11       2026-08-01 09:00:00  1
8         11       2026-08-02 09:00:00  2