Sessionization in SQL: Grouping Events Into Sessions

What sessionization solves

Raw event logs almost never come with a ready-made session_id — just a user identifier and a timestamp on every row. "Session" is a derived concept: a burst of activity separated from the next burst by enough idle time that it's reasonable to call it a new visit. Sessionization is the SQL technique that reconstructs those bursts from nothing but timestamps.

Start with a raw events table:

The first three events are 4-5 minutes apart — clearly one visit. Then a 2 hour 36 minute gap, followed by two more close-together events — clearly a second, separate visit. That's the pattern the query below detects automatically.

Step 1 — Find the gap since the previous event

LAG() pulls the previous row's timestamp onto the current row, scoped per user with PARTITION BY:

SELECT
  user_id,
  event_at,
  LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS prev_event_at
FROM events;

The first row per user gets NULL for prev_event_at — there's nothing before it. That's expected and handled in the next step.

Step 2 — Flag new-session rows

Compare the gap against a threshold. A row starts a new session if it's a user's very first event, or the gap since the previous one exceeds the cutoff:

WITH gaps AS (
  SELECT
    user_id,
    event_at,
    LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS prev_event_at
  FROM events
)
SELECT
  user_id,
  event_at,
  CASE
    WHEN prev_event_at IS NULL THEN 1
    WHEN event_at > prev_event_at + INTERVAL '30 minutes' THEN 1
    ELSE 0
  END AS is_new_session
FROM gaps;

30 minutes is the classic default (it's what Google Analytics uses), but it's just a number to swap for whatever fits the product.

Step 3 — Turn flags into session IDs

A running total of the is_new_session flag, ordered by time, is exactly a session counter: it increments by one every time a new session starts, and stays flat for every row inside the same session.

SUM(is_new_session) OVER (
  PARTITION BY user_id
  ORDER BY event_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_number

This is the same running-total pattern from calculating a running total in SQL — a cumulative sum over a boolean flag is one of the most reusable tricks in analytical SQL.

Full worked example

WITH gaps AS (
  SELECT
    user_id,
    event_at,
    LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS prev_event_at
  FROM events
),
flagged AS (
  SELECT *,
    CASE
      WHEN prev_event_at IS NULL THEN 1
      WHEN event_at > prev_event_at + INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM gaps
)
SELECT
  user_id,
  event_at,
  SUM(is_new_session) OVER (
    PARTITION BY user_id ORDER BY event_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_number
FROM flagged
ORDER BY user_id, event_at;

Exactly the two sessions expected — no manual grouping, no procedural loop, all from timestamps and a threshold.

Session duration and event count

Once every row has a session_number, the usual session metrics are a simple aggregate on top:

SELECT
  user_id,
  session_number,
  MIN(event_at) AS session_start,
  MAX(event_at) AS session_end,
  COUNT(*) AS events_in_session,
  EXTRACT(EPOCH FROM MAX(event_at) - MIN(event_at)) / 60 AS duration_minutes
FROM sessionized
GROUP BY user_id, session_number;

Choosing a gap threshold

There's no universally correct number — it's a judgment call based on how long a natural pause in usage looks for that specific product, often validated by plotting a histogram of gap lengths and looking for a natural cutoff.

Common mistakes

• Forgetting PARTITION BY user_id in the LAG(). Without it, the "previous event" could belong to a completely different user, corrupting every gap calculation.
• Not handling the first event per user. Its LAG() result is NULL, and a raw NULL > threshold comparison silently evaluates to unknown/false in most databases rather than flagging it as a new session — the explicit WHEN prev_event_at IS NULL THEN 1 branch is required.
• Missing the explicit ROWS frame on the running sum. The same RANGE-vs-ROWS tie issue from running totals applies here if two events share an identical timestamp.
• Sessionizing across devices without a cross-device identifier. If a user switches from mobile to desktop, sessionizing purely on a device-scoped ID splits what was really one continuous visit.