Sessionize event data with a clear inactivity rule

Sessionization groups a user's events into visits or activity periods. A common rule starts a new session when the gap from the previous event exceeds a chosen inactivity threshold. The threshold, identity key and exact boundary must be specified; “30-minute sessions” is not enough.

This example starts a new session after a gap strictly greater than 30 minutes. A gap of exactly 30 minutes remains in the existing session. The threshold is an exercise choice, not a universal standard for every product.

The advanced SQL fixture includes event-time and ingestion-time columns so we can distinguish user behaviour from delivery order.

Order by the time of the event

A has events at 10:00, 10:10, 10:40 and 11:11. The first three belong together: their gaps are ten and thirty minutes. The 31-minute gap before 11:11 starts a second session.

C's 11:50 view arrives after its 12:00 purchase event. Sessionization should use event time, so the view still precedes the purchase once both have arrived.

WITH previous AS (
    SELECT event_id, customer_id, event_at,
        LAG(event_at) OVER (
            PARTITION BY customer_id ORDER BY event_at, event_id
        ) AS previous_event_at
    FROM events
), boundaries AS (
    SELECT *,
        CASE
            WHEN previous_event_at IS NULL THEN 1
            WHEN unixepoch(event_at) - unixepoch(previous_event_at) > 1800
                THEN 1
            ELSE 0
        END AS new_session
    FROM previous
), assigned AS (
    SELECT *,
        SUM(new_session) OVER (
            PARTITION BY customer_id ORDER BY event_at, event_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_number
    FROM boundaries
)
SELECT customer_id, session_number,
       MIN(event_at) AS session_start,
       MAX(event_at) AS last_event,
       COUNT(*) AS events,
       unixepoch(MAX(event_at)) - unixepoch(MIN(event_at))
           AS observed_duration_seconds
FROM assigned
GROUP BY customer_id, session_number
ORDER BY customer_id, session_number;

Expected sessions: A has three events spanning 2,400 seconds and a second one-event session spanning zero observed seconds. B has two events spanning 300 seconds. C has two spanning 600 seconds.

The first A session lasts forty minutes even though the inactivity threshold is thirty. The threshold limits gaps between neighbouring events, not total session duration.

SQLite's unixepoch and window functions support the implementation. Date/time functions, window functions.

Interpret duration carefully

The last event timestamp is not necessarily the moment the person stopped using the product. A single-event session has zero observed event span, not proof of zero engagement.

If you extend duration by a timeout after the last event, that is an estimation convention. Label it separately from observed span. Likewise, heartbeat events can change measured duration and session boundaries, so decide whether they qualify as activity.

Session counts can shift after an instrumentation change even if user behaviour does not. A new background event emitted every five minutes may keep sessions artificially alive.

Make identity and duplicate rules explicit

Our partition key is customer ID. A real product may need anonymous-device IDs, authenticated users or an identity mapping. Combining those without a policy can split one person's session or merge activity from different people.

Deduplicate replayed event IDs before counting events. A replay should not increase event count, while two genuine events at the same timestamp may both be valid. The event ID provides a deterministic ordering for timestamp ties, but it does not establish a meaningful behavioural sequence where the source lacks one.

Invalid timestamps, future-clock errors and missing IDs should enter a quality report. Do not force them into a session merely to avoid an empty field.

Late arrivals can change earlier session boundaries

Suppose an event arrives later with a timestamp between two events previously separated by a long gap. It may bridge that gap and merge sessions. A numbered session ID based on cumulative boundaries can therefore change when the source is restated.

For batch analysis, rerun an explicitly defined lookback window and version the result. For streaming systems, use an event-time/watermark policy appropriate to the source's lateness. Do not present ingestion-order sessionization as a faithful reconstruction of behaviour when out-of-order delivery is common.

The exercise does not implement a production stream processor. It demonstrates the semantics that such a system must preserve.

Test the threshold rather than only the happy path

Test gaps of 1,799, 1,800 and 1,801 seconds. Under our rule, only the last starts a new session. If the product instead uses “30 minutes or more,” change the comparison and the documented expected results together.

Check that every eligible event belongs to exactly one session and that session event counts sum to the deduplicated event population.

Exercise: change A's 11:11 event to 11:10. It should join the earlier session under the strictly-greater-than rule. Explain why that one-minute change affects session count but does not remove an event.

NeuraPath's Data Analytics with Generative AI programme covers SQL and data pipelines. Sessionization illustrates how a seemingly small comparison operator can change the business story unless the rule and its tests stay together.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL gaps and islands for consecutive activity days.
• Continue with Calculate rolling averages without hiding missing dates.