Sessionise events with a 30-minute inactivity gap

Table events:

Group each user's events into sessions. A new session starts when the gap since that user's previous event is more than 30 minutes; a gap of exactly 30 minutes stays in the same session. Duplicate timestamps are allowed.

For each session return its number (1, 2, ... per user, in time order), the time of its first event as text YYYY-MM-DD HH24:MI, and the number of events.

Output columns: user_id, session_no, session_start, event_count.

CREATE TABLE events (
  user_id int NOT NULL,
  event_time timestamp NOT NULL
);