# Assign session ids

Shape / table

A variant of [Calculate across rows](https://meridian.online/operations/calculate-across-rows).

Number the sessions in ordered events, starting a new session whenever the gap between consecutive timestamps exceeds a limit.

Page: <https://meridian.online/operations/calculate-across-rows/assign-session-ids>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

number sessions, starting a new one when the gap between events exceeds a limit

```sql
CREATE TEMP TABLE events AS SELECT * FROM (VALUES
  ('u1', TIMESTAMP '2026-03-01 09:00:00'), ('u1', TIMESTAMP '2026-03-01 09:10:00'),
  ('u1', TIMESTAMP '2026-03-01 09:40:00'),   -- exactly 30 minutes after the one before: same session
  ('u1', TIMESTAMP '2026-03-01 10:30:00'), ('u1', TIMESTAMP '2026-03-01 10:35:00'),
  ('u2', TIMESTAMP '2026-03-01 09:05:00'), ('u2', TIMESTAMP '2026-03-01 12:00:00')
) t(user_id, ts);
-- a gap longer than 30 minutes (or no earlier event) starts a session; the running count of starts is the session id
CREATE TEMP TABLE result AS
SELECT user_id, ts, sum(starts_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM (SELECT *, (lag(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL
                 OR ts - lag(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL 30 MINUTE)::INT AS starts_session
      FROM events);
SELECT * FROM result ORDER BY user_id, ts;
SELECT CASE WHEN ((SELECT list(session_id ORDER BY ts) FROM result WHERE user_id = 'u1') = [1, 1, 1, 2, 2] AND (SELECT list(session_id ORDER BY ts) FROM result WHERE user_id = 'u2') = [1, 2]) THEN 'as expected' ELSE error('user u1 should have sessions 1, 1, 1, 2, 2 (a gap of exactly 30 minutes stays in the session) and u2 sessions 1 and 2') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Duckle: [`Sessionize`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25)
- RapidMiner Studio: [`Generate Session ID`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/attribute_generation/generate_session_id.html) (read 2026-09-27)
- Trifacta: [`SESSION Function`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/window-functions.html) (read 2026-09-27)