lesson 54 Advanced product analysis · advanced

Sessionize an event stream

Group consecutive events into user sessions.

the idea

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.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

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

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

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;

Watch for: Strictly greater than 30 minutes is not the same as at least 30 minutes.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

Find the previous timestamp. Order within member. Raw retries remain in this exercise.

step-1.sql

SELECT
  id,
  member_id,
  event_at,
  LAG(event_at) OVER(PARTITION BY member_id ORDER BY event_at,id) AS previous_at
FROM social_events
ORDER BY member_id,event_at,id;

Number the sessions. A new boundary occurs on the first event or a gap >=1800 seconds.

step-2.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 id,member_id,session_number
FROM numbered

Summarize sessions per member. The largest session number is the total number of sessions.

step-3.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