Sessionization — Group Events into Sessions

-- Each event gets a session_id where consecutive events are <= 30 min apart
WITH gaps AS (
    SELECT user_id, event_at,
           CASE
             WHEN EXTRACT(EPOCH FROM event_at -
                  LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at)) > 1800
             THEN 1
             ELSE 0
           END AS new_session
    FROM   events
)
SELECT user_id, event_at,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_at) AS session_id
FROM   gaps;

-- Per-session aggregates from the above
WITH sessions AS (   -- ... same as above
    SELECT user_id, event_at,
           SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_at) AS sid
    FROM   gaps
)
SELECT user_id, sid AS session_id,
       MIN(event_at) AS started_at,
       MAX(event_at) AS ended_at,
       COUNT(*)       AS event_count
FROM   sessions
GROUP  BY user_id, sid;