Split an event stream into sessions with a 30-minute inactivity gap
Table events(user_id, event_time) is a raw event stream. A session ends when a user is inactive for more than 30 minutes; the next event starts a new session. Return the number of sessions per user.
-- sessions per user, split on a >30-minute inactivity gapWrite the query.
Per user, take LAG(event_time) ordered by time. Flag a new session when the gap to the previous event exceeds 30 minutes, or when there is no previous event (LAG is NULL). Summing those start flags per user counts the sessions — each flag marks exactly one session boundary.
- ✗Dividing the total active span by 30 minutes instead of inspecting gaps
- ✗Bucketing by a clock-aligned 30-minute window rather than inter-event gap
- ✗Counting close events as sessions instead of counting session starts
- →How would you assign a session id to each individual event?
- →How does the first event per user avoid being missed as a session start?
Look back one event per user with LAG, mark a session start whenever the gap exceeds the timeout (or there is no prior event), then sum the starts:
WITH gaps AS (
SELECT user_id,
event_time,
CASE
WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
> INTERVAL '30 minutes'
OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
THEN 1 ELSE 0
END AS is_session_start
FROM events
)
SELECT user_id, SUM(is_session_start) AS session_count
FROM gaps
GROUP BY user_id;The gap is between consecutive events, not against a fixed clock grid: two events 25 minutes apart stay in one session even if they straddle a half-hour boundary. The first event per user has a NULL LAG, so it must be forced to start a session, otherwise every user would be undercounted by one. To label each event with a running session number, replace the sum with a SUM(is_session_start) OVER (PARTITION BY user_id ORDER BY event_time).
