Sessionisation

You meet it when#

You have events with a user and a timestamp, and you need to group them into visits, sessions, shifts, or trips — anything where "a new one starts after a gap of N minutes".

The pattern#

Two steps. Flag where a new session begins, then cumulative-sum the flag.

with flagged as (
    select *,
           case when event_at - lag(event_at) over (partition by user_id order by event_at)
                     > interval '30 minutes'
                  or lag(event_at) over (partition by user_id order by event_at) is null
                then 1 else 0
           end as is_new_session
    from events
),
sessioned as (
    select *,
           sum(is_new_session) over (partition by user_id
                                     order by event_at
                                     rows unbounded preceding) as session_seq
    from flagged
)
select user_id,
       session_seq,
       min(event_at)                          as started_at,
       max(event_at)                          as ended_at,
       count(*)                               as events,
       max(event_at) - min(event_at)          as duration
from sessioned
group by user_id, session_seq;

Why the cumulative sum works#

The flag is 1 exactly where a session starts and 0 everywhere else. Summing it from the beginning of the partition gives every event in the first session a 1, every event in the second a 2, and so on. It is a running counter that increments only at boundaries.

This generalises well beyond sessions: any time you can say "a new group starts here", a cumulative sum of that flag numbers the groups. Status changes, price regimes, shift boundaries, deployment windows.

The details that matter#

rows unbounded preceding, explicitly. With an ORDER BY and no frame, the default is RANGE, which includes all peer rows sharing the same timestamp. Two events at the same instant would then both see the same running total, which is usually fine and occasionally is not. Say rows and stop thinking about it.

The is null branch. Without it the first event of each user has a null gap, the case returns 0, and the first session is numbered 0 instead of 1. Harmless until you join on it.

The threshold is a business decision. Thirty minutes is a convention inherited from web analytics, not a law. Look at the distribution of inter-event gaps in your own data and pick the valley.