Assign Session IDs Based on Inactivity Gaps

Group a stream of user events into sessions by labelling each event with a session ID that increments whenever the gap since the previous event exceeds a threshold.

Created May 5, 2026 Last updated May 5, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Split each user's event stream into sessions using a 30-second inactivity threshold

User 1's events at timestamps 0, 10, and 20 are all within 30 seconds of each other, so they belong to session 1. The event at timestamp 2000 is 1980 seconds after the previous event — well above the 30-second threshold — so it starts session 2. User 2's two events (100 and 110) are 10 seconds apart and form a single session 1. The lagged CTE computes the previous timestamp per user with LAG. The flagged CTE converts each row into a 0/1 boundary flag: 1 for the first event per user (prev_ts IS NULL) or any event where the gap exceeds 30 seconds, 0 otherwise. The outer query computes a running sum of that flag — SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY ts) — which starts at 1 (the first session boundary) and increments each time a new session begins, giving every event a stable session ID. To compute per-session statistics such as duration or event count, wrap this query in a CTE and group by (user_id, session_id).

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE user_events (user_id INT, ts INT);

INSERT INTO
  user_events
VALUES
  (1, 0),
  (1, 10),
  (1, 20),
  (1, 2000),
  (2, 100),
  (2, 110);

Validated query Shared across supported engines.

SQL

WITH
  lagged AS (
    SELECT
      user_id,
      ts,
      LAG (ts) OVER (
        PARTITION BY
          user_id
        ORDER BY
          ts
      ) AS prev_ts
    FROM
      user_events
  ),
  flagged AS (
    SELECT
      user_id,
      ts,
      CASE
        WHEN prev_ts IS NULL
        OR ts - prev_ts > 30 THEN 1
        ELSE 0
      END AS is_new_session
    FROM
      lagged
  )
SELECT
  user_id,
  ts,
  SUM(is_new_session) OVER (
    PARTITION BY
      user_id
    ORDER BY
      ts
  ) AS session_id
FROM
  flagged
ORDER BY
  user_id,
  ts;

Expected result Returned rows for the shared example.

user_id	ts	session_id
1	0	1
1	10	1
1	20	1
1	2000	2
2	100	1
2	110	1

Output is identical across all supported engine versions.

Useful when

Where this command helps.

grouping clickstream events into sessions to compute session duration and page depth
identifying separate support interactions within a ticket system based on agent inactivity windows

Explanation

What the command is doing.

Sessionization partitions a user's event stream into discrete activity windows separated by periods of inactivity. The standard SQL approach uses two window operations: LAG to compute the time since the previous event within each user's partition, and a running SUM to count how many session boundaries have occurred up to and including the current row. A session boundary is any event where the gap to the prior event exceeds the inactivity threshold — or where there is no prior event (the very first event per user). Marking each boundary as 1 and all others as 0, then computing SUM(is_boundary) OVER (PARTITION BY user_id ORDER BY ts) produces a monotonically increasing integer within each user partition that resets the count per session. The threshold (30 seconds in this example) is chosen by the analyst. Common choices are 30 minutes for website sessions or 5 minutes for mobile-app sessions. Since integer timestamps avoid date-arithmetic dialect differences, the same CTE works across all engines that support window functions.

Assign Session IDs Based on Inactivity Gaps

Split each user's event stream into sessions using a 30-second inactivity threshold

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.