Shape / table · All operations

Assign session ids

A variant of Calculate across rows

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

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Duckle (1)
  • Sessionizeread 2026-09-25

    assign a session id by inactivity gap within a partition

RapidMiner Studio (1)
  • Generate Session IDread 2026-09-27

    Add a session id that increments when the gap between consecutive dates exceeds a maximum

Trifacta (1)
  • SESSION Functionread 2026-09-27

    Assign a session id from sorted timestamps and a rolling time gap

Background

Time-oriented session reconstruction ends a session once a set period of user inactivity, the inactivity threshold, is reached.[1] In time-oriented session reconstruction, further requests from the same user after the threshold are considered a second session.[1] A common value for the inactivity threshold is 30 minutes.[1] In Google Analytics, a session times out by default after 30 minutes of user inactivity.[2]

When to use it

Sessions per user can measure website usage, and session length and user actions per session are also used as metrics.[1] Sessions are also used for operational analytics, data anonymisation, identifying networking anomalies and synthetic workload generation.[1]

Some have argued that a 30-minute threshold produces artefacts around naturally long sessions and have experimented with other thresholds.[1] Published SQL for sessions differs at the boundary, one query starting a new session at a gap of 30 minutes or more and another only at more than 30 minutes.[3][4] Say whether a gap exactly equal to the threshold continues the session or starts a new one, and keep that choice the same wherever sessions are counted. (Meridian's view)

Adobe Analytics ends a session after 12 hours, capping its length.[4] In Google Analytics, there is no limit to how long a session can last.[2] Partition by whoever's activity forms the session, a user, a device or a machine, or two people's events will interleave into one sequence. (Meridian's view)

The method

One SQL method takes each event's gap from the user's previous event with LAG, over a window partitioned by user and ordered by timestamp.[3][4] The LAG-based method flags each event whose gap passes the threshold with 1 and every other event with 0.[3] A user's first event has no previous event, so LAG gives it an empty interval, and one published query counts that empty gap as the start of a session.[3][4]

Apache MADlib's sessionize function starts a new session when the time between subsequent events is longer than its max_time interval.[5] Apache MADlib's sessionize function sorts the timestamps in ascending order within each partition and numbers the sessions 1, 2 and so on.[5]

Compared with the alternatives

Navigation-oriented approaches to session reconstruction use the structure of a website, such as starting a new session when a request's HTTP referer is not a page already in the session.[1]

Sources

  1. [1]Session (web analytics) - Wikipediasecondary source
  2. [2]About Analytics sessions - Analytics Helpdocumentation
  3. [3]Sessionization in SQL, Hive, and Python - Dataiku 15 - Developer Guidesecondary source
  4. [4]skeptric - Create User Sessions with SQLsecondary source
  5. [5]MADlib: Sessionizedocumentation