Sessionise a clickstream with a 30-minute rule

Product analytics defines a session as a run of a user's events where no two consecutive events are more than 30 minutes apart. A gap of exactly 30 minutes keeps the session going; a gap of more than 30 minutes starts a new one.

From click_events, return user_id, session_no (1, 2, 3... per user), session_start, session_end, events (number of events) and duration_minutes (end minus start, in minutes, 1 decimal). Order by user_id, then session_no.

1. Step 1: `LAG(event_time)` gives each event the time of the user's previous event.
2. Step 2: Flag an event as the start of a new session when there is no previous event or the gap is strictly more than 30 minutes. A gap of exactly 30 minutes (user 1 at 10:20 to 10:50) therefore stays in the same session.
3. Step 3: A running `SUM` of the flags per user turns the 0/1 markers into session numbers 1, 2, 3.
4. Step 4: Finally group by `user_id, session_no` to compute the start, end, event count and duration.

WITH ordered AS (
    SELECT user_id,
           event_time,
           LAG(event_time) OVER (
               PARTITION BY user_id ORDER BY event_time
           ) AS prev_time
    FROM click_events
),
flagged AS (
    SELECT user_id,
           event_time,
           CASE
             WHEN prev_time IS NULL
               OR event_time - prev_time > INTERVAL '30 minutes' THEN 1
             ELSE 0
           END AS new_session
    FROM ordered
),
numbered AS (
    SELECT user_id,
           event_time,
           SUM(new_session) OVER (
               PARTITION BY user_id
               ORDER BY event_time
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS session_no
    FROM flagged
)
SELECT user_id,
       session_no,
       MIN(event_time) AS session_start,
       MAX(event_time) AS session_end,
       COUNT(*)        AS events,
       ROUND(EXTRACT(EPOCH FROM MAX(event_time) - MIN(event_time)) / 60.0, 1) AS duration_minutes
FROM numbered
GROUP BY user_id, session_no
ORDER BY user_id, session_no;