SQL Sessionization

Group individual events into visits — a new session starts whenever the gap since the last event is too long.

What & Why

Raw event logs don't come with a "session ID" — you build one by comparing each event's timestamp to the previous event's (via LAG), and starting a new session whenever the gap exceeds some threshold, like 30 minutes.

See How It Works

BUSINESS QUESTION

Combine feedback and feature-use activity, then keep events within 30 minutes in the same derived session.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

id	feature_id	user_id	used_at	session_id
7001	1	101	2024-01-25 12:20:00+00	sess_101a
7002	2	102	2024-02-20 15:45:00+00	sess_102a
7003	1	103	2024-03-18 09:10:00+00	sess_103a
7004	3	104	2024-04-08 17:35:00+00	sess_104a

EXAMPLE QUERY

WITH activity_events AS (
  SELECT id::bigint AS event_id, user_id, 'feedback' AS event_name, created_at
  FROM product.feedback
  UNION ALL
  SELECT id AS event_id, user_id, 'feature_used' AS event_name, used_at AS created_at
  FROM product.feature_usage
),
sequenced AS (
  SELECT
    *,
    LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at, event_id) AS previous_event_at
  FROM activity_events
),
boundaries AS (
  SELECT
    *,
    CASE
      WHEN previous_event_at IS NULL OR created_at - previous_event_at > INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM sequenced
)
SELECT
  event_id,
  user_id,
  event_name,
  created_at,
  SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY created_at, event_id) AS derived_session_number
FROM boundaries
ORDER BY user_id, created_at, event_id;

RESULT — feedback and feature use grouped into sessions

event_id	user_id	event_name	created_at	derived_session_number
1	101	feedback	2024-01-25 12:15:00+00	1
7001	101	feature_used	2024-01-25 12:20:00+00	1
2	102	feedback	2024-02-20 15:40:00+00	1
7002	102	feature_used	2024-02-20 15:45:00+00	1
3	103	feedback	2024-03-18 09:05:00+00	1
7003	103	feature_used	2024-03-18 09:10:00+00	1
4	104	feedback	2024-04-08 17:30:00+00	1
7004	104	feature_used	2024-04-08 17:35:00+00	1

Each user's feature-use event occurs five minutes after feedback, so both rows remain inside derived session 1.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice