Sessionization groups events into user sessions. Common in analytics.
The pattern:
WITH with_prev AS (
SELECT user_id, event_time,
LAG(event_time) OVER (
PARTITION BY user_id ORDER BY event_time
) as prev_time
FROM events
),
with_session_start AS (
SELECT *,
CASE WHEN event_time - prev_time > INTERVAL '30 min'
OR prev_time IS NULL THEN 1 ELSE 0
END as new_session
FROM with_prev
)
SELECT *, SUM(new_session) OVER (
PARTITION BY user_id ORDER BY event_time
) as session_id
FROM with_session_start