Sessionization with SQL window functions

In the window functions guide I listed sessionization as one of the things that belongs in the query rather than in Python or a BI tool. This is the worked version of that claim. It's the most useful non-obvious thing you can do with window functions, and it's built entirely out of the primitives from that post — LAG, a running SUM, and an explicit frame clause.

Sessionization is the problem of turning a flat stream of timestamped events into sessions: bounded groups of activity where a gap of inactivity longer than some threshold — say 30 minutes — starts a new one. Every product analytics tool does this internally. When you own the events, you can do it yourself in a handful of lines, and you should, because the logic is trivial and the alternatives (self-joins, procedural loops, exporting to pandas) are all worse.

The shape of the problem#

Start with an events table: one row per user action, with a user and a timestamp.

user_id | event_time
--------+---------------------
 u_1    | 2026-07-01 09:00:00
 u_1    | 2026-07-01 09:04:00   -- 4 min later, same session
 u_1    | 2026-07-01 11:30:00   -- 2.5 h gap, new session
 u_1    | 2026-07-01 11:31:00
 u_2    | 2026-07-01 09:02:00

The rule: within a user, if the gap since the previous event is more than 30 minutes, that event begins a new session. The first event a user ever has is always a new session. Everything else continues the current one.

Step 1: measure the gap with LAG#

The cornerstone rule holds — anytime you're comparing a row to the one before it in time, reach for LAG, not a self-join. Partition by user so the reach-back never crosses from one user into another.

WITH ordered AS (
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (
      PARTITION BY user_id
      ORDER BY event_time
    ) AS prev_event_time
  FROM events
)
SELECT * FROM ordered;

Now every row knows the timestamp of the user's previous event. For the first event of each user, prev_event_time is NULL — which is exactly the signal we want for "no previous activity."

Step 2: flag the session boundaries#

A row starts a new session if there's no previous event, or if the gap is over the threshold. That's a single CASE.

flagged AS (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN prev_event_time IS NULL
        OR event_time - prev_event_time > INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS is_new_session
  FROM ordered
)

In DuckDB and Postgres, subtracting two timestamps yields an INTERVAL, and comparing intervals works directly. On BigQuery you'd write TIMESTAMP_DIFF(event_time, prev_event_time, MINUTE) > 30; on Snowflake, DATEDIFF('minute', prev_event_time, event_time) > 30. Same idea, dialect-specific spelling. Each qualifying row now carries a 1; every continuation carries a 0.

Step 3: number the sessions with a running SUM#

Here's the move that makes the whole thing click. A cumulative SUM over those 0/1 flags produces a session counter that increments exactly when a new session starts and holds steady through every continuation:

SELECT
  user_id,
  event_time,
  SUM(is_new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_number
FROM flagged;

Run it against the sample and u_1's first two events land in session 1, the two after the long gap in session 2. The general name for this technique is gaps-and-islands: the boundary flags mark the gaps, the running total labels the islands.

Note the frame clause: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, written out in full. This is the running-total gotcha from the cornerstone showing up in a place where it genuinely bites. If you omit the frame, the default is RANGE, which lumps together every row sharing the same ORDER BY value into one peer group. Event streams routinely contain two events with an identical timestamp — a click and its page view fire the same millisecond — and under RANGE those rows would get the same running total, quietly merging what should be a clean step. Write ROWS and the counter steps one row at a time, exactly as sessionization needs.

A session_number is only unique within a user, so the durable session key is the pair — concatenate them (user_id || '-' || session_number) or just carry both columns forward.

Rolling up to a sessions table#

The query above keeps one row per event, annotated with its session. That's the "aggregate without collapsing" half of window functions. To get one row per session — the table you'd actually report on — hand the result to a plain GROUP BY, which is the collapsing half:

WITH sessionized AS (
  -- the step 3 query
)
SELECT
  user_id,
  session_number,
  MIN(event_time)                    AS session_start,
  MAX(event_time)                    AS session_end,
  MAX(event_time) - MIN(event_time)  AS session_duration,
  COUNT(*)                           AS event_count
FROM sessionized
GROUP BY user_id, session_number;

Window functions assign the sessions; GROUP BY summarises them. Two passes, each doing the thing it's good at. From here, session duration and event counts feed straight into engagement metrics, funnels, and cohort retention — all of which are just more window functions on top of this table.

Why keep it in SQL#

The explainer artifact from the cornerstone is worth revisiting with this in mind — pick the running SUM and hover the rows to watch the frame slide, because that sliding frame is precisely the mechanism numbering your sessions.

The reason to do this in the warehouse rather than in application code is the same reason the whole analytical layer belongs close to the data: it's expressed once, it runs where the rows already live, and an engine like DuckDB executes it fast even over large event tables. Pushing it into a procedural loop or a dataframe means re-implementing ordering, partitioning, and gap detection that the database already does correctly. This is a small instance of a bigger habit I keep coming back to in how I build AI-native — move the logic to where it's cheapest to get right, and let declarative tools carry the weight.

Sessionization looks like a hard problem the first time you meet it and turns out to be three window functions in a trench coat. Learn the gaps-and-islands shape once — flag the gaps, running-sum the islands, group to summarise — and you'll reach for it far beyond sessions.