SQL sessionization and event-sequence analysis: turning events into user journeys

Event rows are not yet user journeys

Many modern systems record activity as a stream of events. The event table tells us what happened, but it does not automatically tell us which events belong to the same visit or journey. Consider the following events:

For user 1, the first three events occur fairly close together (09:00, 09:05, 09:12), then there is a much longer gap from 09:12 to 10:05. Should 10:05 belong to the same visit? That depends on the session definition. For this article, a new session begins when more than 30 minutes have passed since the user’s previous event. With that rule, user 1 has one session for 09:00 to 09:12 and a second for 10:05 to 10:10.

Define the event sequence, then the gap

Before SQL can decide whether two events belong together, it must know which came first. For each user, order by event_at, event_id (event_id breaks ties, because two events can share a timestamp). Then find the previous event time with LAG:

SELECT
    event_id,
    user_id,
    event_at,
    event_name,
    LAG(event_at) OVER (
        PARTITION BY user_id
        ORDER BY event_at, event_id
    ) AS previous_event_at
FROM user_events
ORDER BY user_id, event_at, event_id;

For each row, the inactivity gap is event_at - previous_event_at. For user 1: no previous at 09:00, then 5, 7, 53, and 5 minutes. The rule “more than 30 minutes of inactivity starts a new session” means the 10:05 event (a 53-minute gap) begins a new session.

Mark session starts

Convert the rule into a flag, where the first event for every user must start a session because there is no previous event:

WITH ordered_events AS (
    SELECT
        event_id, user_id, event_at, event_name,
        LAG(event_at) OVER (
            PARTITION BY user_id
            ORDER BY event_at, event_id
        ) AS previous_event_at
    FROM user_events
)
SELECT
    event_id, user_id, event_at, event_name,
    CASE
        WHEN previous_event_at IS NULL THEN 1
        WHEN event_at - previous_event_at > INTERVAL '30 minutes' THEN 1
        ELSE 0
    END AS new_session
FROM ordered_events
ORDER BY user_id, event_at, event_id;

We have identified where sessions begin. Now we need to assign a session number.

Turn session starts into session IDs

The pattern is the same one used for gaps and islands: cumulatively sum the session-start flag:

WITH ordered_events AS (
    SELECT
        event_id, user_id, event_at, event_name,
        LAG(event_at) OVER (
            PARTITION BY user_id
            ORDER BY event_at, event_id
        ) AS previous_event_at
    FROM user_events
),
marked_events AS (
    SELECT
        *,
        CASE
            WHEN previous_event_at IS NULL THEN 1
            WHEN event_at - previous_event_at > INTERVAL '30 minutes' THEN 1
            ELSE 0
        END AS new_session
    FROM ordered_events
)
SELECT
    event_id, user_id, event_at, event_name,
    SUM(new_session) OVER (
        PARTITION BY user_id
        ORDER BY event_at, event_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS session_number
FROM marked_events
ORDER BY user_id, event_at, event_id;

Each row now belongs to a session. The number session 1 is unique only within one user: user 1 has a session 1 and user 2 also has a session 1. So the real session identity is user_id + session_number. For analytical SQL, keeping that pair is usually enough.

Sessionizing events without partitioning by user

Suppose someone writes LAG(event_at) OVER (ORDER BY event_at, event_id) instead of partitioning by user_id. Now the previous event may belong to another user. Between user 1’s 10:10 search and user 2’s 11:00 app_open, SQL may compute 11:00 - 10:10 = 50 minutes and use that to decide whether user 2 begins a session.

LAG(event_at) OVER (
    ORDER BY event_at, event_id   -- no PARTITION BY user_id
)

-- User 1: 10:10 search
-- User 2: 11:00 app_open   -> gap computed as 50 min across users

-- A: sessions cannot use timestamps
-- B: the 10:10 event belongs to another user
-- C: 50 minutes is not longer than 30 minutes
-- D: LAG cannot be used for sessionization

The 30-minute boundary must be defined precisely

Summarizing sessions

Once every event has user_id and session_number, ordinary aggregation turns event rows into session rows. With the sessionized data in a CTE:

SELECT
    user_id,
    session_number,
    MIN(event_at) AS session_start,
    MAX(event_at) AS session_end,
    MAX(event_at) - MIN(event_at) AS session_duration,
    COUNT(*) AS event_count
FROM sessionized_events
GROUP BY user_id, session_number
ORDER BY user_id, session_start;

Now one row is one session instead of one event. Session duration is MAX(event_at) - MIN(event_at); for user 2 session 1, that is 11:25 - 11:00 = 25 minutes.

Entry, exit, position, and transitions

Rank events within each session to find the entry event (ROW_NUMBER() OVER (PARTITION BY user_id, session_number ORDER BY event_at, event_id), keep rn = 1) and the exit event (reverse the ordering to event_at DESC, event_id DESC, keep rn = 1). For our sample the entry event is app_open in every session, and the exit events are add_to_cart, search, purchase, and app_open. In web analytics the same pattern finds the landing page or the last recorded page. Note that “exit event” means the last event we observed, not proof the user intentionally left afterward.

Numbering every event gives the explicit journey. For user 2 session 1:

SELECT
    user_id, session_number, event_at, event_name,
    ROW_NUMBER() OVER (
        PARTITION BY user_id, session_number
        ORDER BY event_at, event_id
    ) AS event_position
FROM sessionized_events
ORDER BY user_id, session_number, event_position;

Comparing consecutive events

LAG(event_name) returns the previous action and LEAD(event_name) the next, both partitioned by user_id, session_number. This gives transitions such as app_open -> view_product and view_product -> checkout_started. Grouping current and next event pairs with COUNT(*) reveals the most common transitions:

WITH transitions AS (
    SELECT
        event_name AS current_event,
        LEAD(event_name) OVER (
            PARTITION BY user_id, session_number
            ORDER BY event_at, event_id
        ) AS next_event
    FROM sessionized_events
)
SELECT
    current_event,
    next_event,
    COUNT(*) AS transition_count
FROM transitions
WHERE next_event IS NOT NULL
GROUP BY current_event, next_event
ORDER BY transition_count DESC, current_event, next_event;

When the event order cannot be trusted

Repeated events matter. Three consecutive view_product rows may be three real page views (“how many product pages did the user browse?”) or noise to collapse (“what sequence of distinct state changes occurred?”). Use LAG(event_name) to detect when event_name = previous_event and optionally remove repeated adjacent states, but only if repeated actions are not meaningful. That is different from duplicate events: two rows with the same event_id are a data-quality duplicate, while the same event name at different times and IDs may be two genuine actions. Deduplicate by the actual event identity, not by matching names, and sessionize on clean data.

The same timestamp can hide an undefined sequence. If add_to_cart and checkout_started both occur at 11:05:00 and you order only by event_at, their order is unresolved, which matters for transition analysis. Use a reliable secondary key (event_at, event_id, a source sequence number, or an event version). If event order changes the meaning, timestamp alone may not be enough.

Sessions can cross midnight, and are not daily activity

A 23:55 view_product and a 00:05 add_to_cart are only ten minutes apart, so under a 30-minute rule they belong to the same session even though the calendar date changed. Do not define a session as one user plus one calendar day unless that is the business rule; boundaries come from the inactivity condition, not automatically from midnight. Sessionization is also a different grain from daily activity: one row per user per day suits daily retention and streaks, while one row per user session suits visits, duration, entry/exit, and behaviour sequences. One customer can have three sessions in the same calendar day, so do not substitute one grain for the other.

How to spot sessionization problems

Think about sessionization and event-sequence analysis when the requirement says user sessions, visits, inactivity timeout, session duration, events per session, entry or exit event, landing page, event path, previous or next action, common transitions, journey sequence, or behaviour before purchase.

Watch for these common mistakes:

• PARTITION BY is missing and events cross users.
• Sessions are split at midnight even though inactivity is the real rule.
• Exactly 30 minutes is handled differently from the business definition.
• Event order relies on timestamps that can tie.
• Ingestion time is used when event time should define the sequence.
• Duplicate events inflate event counts.
• Repeated legitimate actions are removed as if they were duplicates.
• One-event sessions are interpreted as zero user attention.
• Session metrics are calculated at event grain.
• Events from separate sessions are connected into one transition sequence.

Answer five questions for sessionization

Order events within the entity
      v
Compare each event with the previous event
      v
Mark inactivity breaks
      v
Cumulatively count the breaks
      v
Assign each event to a session
      v
Analyze each session or its internal sequence

One more prediction

These events come from one user. The rule: start a new session when more than 30 minutes pass since the previous event. How many sessions are there?

Gaps:
09:00 -> 09:10   10 minutes
09:10 -> 09:35   25 minutes
09:35 -> 10:06   31 minutes

Rule: new session when gap > 30 minutes

-- A: 1    B: 2    C: 3    D: 4

Summary

Sessionization turns individual events into journeys. Define the entity (PARTITION BY user_id), define event order (ORDER BY event_at, event_id), use LAG(event_at) to find the previous event, mark a new session when the inactivity rule is met (previous IS NULL or gap > INTERVAL '30 minutes'), then cumulatively count the breaks with SUM(new_session) OVER (...). Once every event has a session ID, you can derive session start and end, observed duration, event count, entry and exit events, event position, previous and next events, common transitions, and conversion within a session.