milestone 3/4 Sessionize an event stream
Group consecutive events into user sessions.
Social Product Analytics · Clean event retries, reconstruct ordered funnels, sessionize activity, and compare experiment conversions.
approach
A session starts on a member’s first event or after a gap of at least 30 minutes. LAG finds the prior timestamp, CASE flags boundaries, and a running SUM numbers sessions. Here we use physical rows, including retries. Identical timestamps do not start a new session.
worked-example.sql
WITH previous AS(
SELECT
*,
LAG(event_at) OVER(PARTITION BY member_id ORDER BY event_at,id) AS previous_at
FROM social_events
), marked AS(
SELECT
*,
CASE
WHEN previous_at IS NULL
OR unixepoch(event_at)-unixepoch(previous_at)>=1800
THEN 1
ELSE 0
END AS new_session
FROM previous
), numbered AS(
SELECT
*,
SUM(new_session) OVER(PARTITION BY member_id ORDER BY event_at,id ROWS UNBOUNDED PRECEDING) AS session_number
FROM marked
)
SELECT member_id,MAX(session_number) AS session_count
FROM numbered
GROUP BY member_id;data for this milestone
social_events6 columns · 22 rows
| idinteger · key | event_keytext | member_idinteger | event_typetext | event_attext | ingested_attext |
|---|---|---|---|---|---|
| 1 | a | 1 | view | 2025-01-01 10:00:00 | 2025-01-01 10:00:01 |
| 2 | b | 1 | signup | 2025-01-01 10:03:00 | 2025-01-01 10:03:01 |
| 3 | c | 1 | purchase | 2025-01-01 10:05:00 | 2025-01-01 10:05:01 |
| 4 | a | 1 | view | 2025-01-01 10:00:00 | 2025-01-01 10:10:00 |
| 5 | d | 2 | view | 2025-01-02 09:00:00 | 2025-01-02 09:00:01 |
showing 5 of 22 rows