lesson 54 Advanced product analysis · advanced

Sessionize an event stream

Group consecutive events into user sessions.

the idea

Sessionization groups each user’s events into sessions: a new session starts at the first event or after a gap of at least 30 minutes. Mark starts with LAG, then turn the marks into session numbers with a running SUM.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Measure idle time between events. Run this. LAG() on occurred_at shows minutes since user 100’s previous event—the input to a 30-minute session rule.

   step-1.sql Run

   sql›

   SELECT
     user_id,
     occurred_at,
     ROUND((julianday(occurred_at) - julianday(LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id))) * 1440, 1) AS minutes_since_previous
   FROM events
   WHERE user_id = 100;

2. 2

   Compare to stored sessions. Run this. The sessions table is the product’s own log for the same user—your SQL rebuild should resemble these boundaries.

   step-2.sql Run

   sql›

   SELECT session_id, started_at, ended_at
   FROM sessions
   WHERE user_id = 100;

3. 3

   Mark where a session starts. Run this. starts_session is 1 on the first event or after a gap of at least 30 minutes; a running sum of those flags numbers sessions next.

   step-3.sql Run

   sql›

   WITH g AS (
     SELECT
       user_id,
       occurred_at,
       (julianday(occurred_at) - julianday(LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id))) * 1440 AS gap
     FROM events
     WHERE user_id = 100
   )
   SELECT
     occurred_at,
     ROUND(gap, 1) AS minutes_since_previous,
     CASE
       WHEN gap IS NULL OR gap >= 30 THEN 1
       ELSE 0
     END AS starts_session
   FROM g;

4. 4

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT
     user_id,
     occurred_at,
     LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id) AS previous_at
   FROM events
   WHERE user_id = 100;

common mistake

Ordering by received_at instead of occurred_at splits sessions when late deliveries arrive out of order.