Sessionize Clickstream Events

Difficulty: Medium · Topics: sessionization, lag, running-sum · Asked at: Google, Amazon, Spotify, Pinterest

Problem

A new session starts when a user has been inactive for more than 30 minutes. For every session return user_id, session_no (1, 2, … per user), session_start, session_end, events and duration_min. Order by user and session_no.

Schema and sample data

CREATE TABLE events (user_id INTEGER, event_ts TEXT, page TEXT);
INSERT INTO events VALUES
(1,'2026-04-01 10:00:00','home'),(1,'2026-04-01 10:05:00','search'),(1,'2026-04-01 10:20:00','product'),
(1,'2026-04-01 11:30:00','home'),(1,'2026-04-01 11:31:00','cart'),
(2,'2026-04-01 09:00:00','home'),(2,'2026-04-01 09:30:00','product'),(2,'2026-04-01 10:00:01','checkout'),
(3,'2026-04-01 23:50:00','home');

Expected output

Hints

Solution

WITH gaps AS (
  SELECT *,
         ROUND((julianday(event_ts) - julianday(LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts))) * 86400) AS gap_sec
  FROM events
), flagged AS (
  SELECT *, CASE WHEN gap_sec IS NULL OR gap_sec > 1800 THEN 1 ELSE 0 END AS is_new
  FROM gaps
), numbered AS (
  SELECT *, SUM(is_new) OVER (PARTITION BY user_id ORDER BY event_ts ROWS UNBOUNDED PRECEDING) AS session_no
  FROM flagged
)
SELECT user_id, session_no,
       MIN(event_ts) AS session_start, MAX(event_ts) AS session_end, COUNT(*) AS events,
       ROUND((julianday(MAX(event_ts)) - julianday(MIN(event_ts))) * 1440, 1) AS duration_min
FROM numbered
GROUP BY user_id, session_no
ORDER BY user_id, session_no;

Explanation

User 2’s events are exactly 30:00 and then 30:01 apart: the first gap stays in the session (> 30 means strictly greater), the second starts a new one. Boundary conditions like this are what interviewers check. Ask “is 30 minutes exactly a new session?”

julianday arithmetic is floating point: 30 minutes can come out as 30.0000001. Rounding to whole seconds before comparing avoids an off-by-epsilon boundary bug (in Postgres/Spark, compare intervals or epoch seconds directly).

This flag + running sum pattern is the general tool for “start a new group when a condition is met”.

Follow-up questions