Session ids for app events

Product analytics counts “sessions”, but the app never sends a session id. You have to derive one.

The rule: a user’s events belong to the same session until there’s a gap of more than 30 minutes between one event and the next. The event after that gap starts a new session.

Tag every row of events with its session number:

column	type	meaning
event_id	integer	the event
user_id	integer	the user
session_id	integer	1 for the user’s first session, 2 for the next, and so on

Edge cases:

• A gap of exactly 30 minutes does not start a new session. 31 minutes does.
• Order a user’s events by event_ts. Some timestamps repeat (duplicate ingests). Break those ties with event_id. A repeated timestamp is a gap of zero, so it never starts a session.
• Every row of events appears once in the output, duplicates included.

Row order doesn’t matter.

Tables: events.

Approach

Flag the events that start a session, then take a running sum of the flags.

WITH gaps AS (
  SELECT event_id,
         user_id,
         event_ts,
         LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts, event_id) AS prev_ts
  FROM events
),
flags AS (
  SELECT event_id,
         user_id,
         event_ts,
         CASE
           WHEN prev_ts IS NULL                          THEN 1
           WHEN event_ts - prev_ts > INTERVAL 30 MINUTE  THEN 1
           ELSE 0
         END AS is_new_session
  FROM gaps
)
SELECT event_id,
       user_id,
       SUM(is_new_session) OVER (
         PARTITION BY user_id
         ORDER BY event_ts, event_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_id
FROM flags;

Why it works

Each user’s first event gets a 1 because there’s nothing before it. Every later event gets a 1 only if the gap behind it is over 30 minutes. So a user’s flags look like 1 0 0 0 1 0 1 0 0, and the running sum is 1 1 1 1 2 2 3 3 3: a session number on every row.

Two details keep it exact. > rather than >= puts a 30-minute gap inside the session. And the frame is ROWS, not the default RANGE. With RANGE, rows that tie on the ORDER BY value are summed together, which is harmless here because event_id breaks every tie, but ROWS says what you mean.

Comparing two timestamps as an interval is safer than counting minute boundaries. 09:00:40 to 09:30:50 is 30 minutes and 10 seconds, which is over the limit. date_diff('minute', ...) counts clock-minute boundaries crossed and says 30, so it would keep the event in the old session.

Under the hood

Both windows share the same PARTITION BY and ORDER BY, so DuckDB sorts the events once per user and reuses the order. LAG reads one row back. The running SUM keeps a single accumulator per partition and adds one value per row, so it’s linear after the sort. This “flag, then cumulative sum” trick is the same one streaming systems use for session windows, except they hold the partition open until the gap timer fires.