Concept · SQL & Querying

Sessionization = flag boundaries (gap > timeout) then running-SUM the flags into a session id — and the partition must be per entity or sessions bleed across them.

A running sum of 0/1 flags is a counter that only advances at boundaries — every row between two boundaries inherits the same count, which is exactly a session id. The leading NULL gap folds in for free (NULL → flag 1: the first event starts session 1). The two classic failures are both partition failures: omit PARTITION BY on either window and one entity's counter continues into the next; or order by the wrong column and boundaries land mid-burst.

See it break

obs

volunteer	ts
A	2024-05-01 09:00
A	2024-05-01 09:40
A	2024-05-01 12:30
B	2024-05-01 09:10
B	2024-05-01 09:30

The trap

WITH
  flagged AS (
    SELECT
      volunteer,
      ts,
      CASE
        WHEN ts - LAG (ts) OVER (
          ORDER BY
            ts
        ) > INTERVAL '2 hours'
        OR LAG (ts) OVER (
          ORDER BY
            ts
        ) IS NULL THEN 1
        ELSE 0
      END AS new_session
    FROM
      obs
  )
SELECT
  volunteer,
  strftime(ts, '%H:%M') AS mark,
  SUM(new_session) OVER (
    ORDER BY
      ts
  ) AS session_id
FROM
  flagged
ORDER BY
  ts;

The fix

WITH
  flagged AS (
    SELECT
      volunteer,
      ts,
      CASE
        WHEN ts - LAG (ts) OVER (
          PARTITION BY
            volunteer
          ORDER BY
            ts
        ) > INTERVAL '2 hours'
        OR LAG (ts) OVER (
          PARTITION BY
            volunteer
          ORDER BY
            ts
        ) IS NULL THEN 1
        ELSE 0
      END AS new_session
    FROM
      obs
  )
SELECT
  volunteer,
  strftime(ts, '%H:%M') AS mark,
  SUM(new_session) OVER (
    PARTITION BY
      volunteer
    ORDER BY
      ts
  ) AS session_id
FROM
  flagged
ORDER BY
  volunteer,
  ts;

INPUT		Your query	The fix
A	09:00	1	1
B	09:10	1	1
B	09:30	1	2
A	09:40	1	1
A	12:30	2	1

Without PARTITION BY, one volunteer's session count ran straight into the next volunteer's rows — sessionization needs both windows partitioned per entity or the sessions bleed across them.

The rule

The two-step build (gap → flag → running SUM), both windows partitioned per entity, with the timeout as a named interval, not a magic number.

Shows up elsewhere

LAG detects the gap The islands trick

Try one →Prove it in practice →