Sessionization: Grouping Events into User Sessions in SQL

Web analytics tools like Google Analytics automatically talk about "Sessions". But what if you are working with raw event logs (e.g., from Segment or Mixpanel) in your data warehouse? You have to build the logic yourself.

A Session is typically defined as a group of interactions by one user where no two interactions are more than X minutes apart (standard is 30 minutes).

A Real Business Scenario: Rebuilding Visits from Warehouse Events

Product teams often move from GA-style dashboards to warehouse-native event analysis. The moment that happens, "session" stops being a built-in metric and becomes a definition you have to own.

Typical use cases:

• measuring conversion rate per visit instead of per raw event
• calculating bounce rate from your own clickstream tables
• understanding how long users stay active before going idle

If you skip sessionization, many downstream metrics become noisy or misleading. Event counts alone do not tell you how many distinct visits happened or where one visit ended and the next began.

The Logic

1. Calculate Time Gaps: For each row, calculate the difference between the current timestamp and the previous timestamp for that user.
2. Flag New Sessions: If the gap > 30 minutes, mark it as the start of a new_session (flag = 1). Otherwise, it's part of the same session (flag = 0).
3. Accumulate Flags: A running sum of these flags gives you a unique Session ID for each user.

Step 1: Time Between Events

LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp)

We compare timestamp vs prev_timestamp.

Step 2: The New Session Flag

CASE
    WHEN (timestamp - prev_timestamp) > (30 * 60) THEN 1  -- > 30 mins
    ELSE 0
END as is_new_session

A Common Mistake: Forgetting the Definition Is a Product Choice

Many analysts copy the 30-minute inactivity rule without checking whether it fits the product.

That can be wrong for:

• B2B tools where users often pause for longer than 30 minutes while still working in the same task
• media products where short bursts of interaction may belong to the same content session
• backend event pipelines where timestamps reflect ingestion time rather than true user activity time

Sessionization is not just a SQL pattern. It is a behavioral definition. The query is only correct if the timeout rule matches how your business wants to interpret a visit.

Interactive Playground

In this example, we'll see a user clicking around. We want to identify distinct "visits" based on a 30-minute timeout. Note: For text timestamps in SQLite, we use julianday math. 30 minutes is roughly 0.0208 days.

Why This Matters

Once you have session_id, you can calculate:

• Average Session Duration: MAX(time) - MIN(time) per session.
• Bounce Rate: Sessions with only 1 event.
• Conversion Rate: Sessions that end in a purchase.

Boundary and Performance Notes

Sessionization queries are usually built on large event tables, so both logic and scale matter.

• Event timestamps should be normalized to a consistent timezone before you compare gaps.
• Late-arriving events can re-open or split sessions if your warehouse backfills data after the first run.
• Large partitions per user require sorting all events in order, so indexes or clustering by (user_id, event_time) matter in engines that support them.
• If the same user can have overlapping devices or tabs, a user-level session definition may be too coarse; device or stream identifiers may need to be part of the partition.

The query pattern is stable, but the correctness of the output depends heavily on event quality and the precise business definition of inactivity.

When NOT to Use Simple Sessionization

The inactivity-gap model is a strong default, not a universal truth.

Avoid treating it as the whole answer when:

• the product has explicit start/end events that already define a session more accurately
• you need attribution windows rather than visit windows
• you are analyzing batch jobs, backend workflows, or machine events where "session" is not a meaningful unit

In those cases, a custom state model or explicit lifecycle markers may represent the process better than a 30-minute timeout.

Official References

• PostgreSQL window function documentation for LAG and related ordered-row semantics.
• SQLite window function documentation for SQLite support and syntax details.
• Google Analytics 4 session definition reference for a product example of how commercial analytics systems define sessions and inactivity windows.

Conclusion

Sessionization is a fundamental transformation in clickstream analytics. By mastering this pattern, you unlock the ability to analyze user behavior at the "visit" level, which is far more meaningful than independent "hits".

Related Articles

• Building Conversion Funnels in SQL for the stage-based analysis that often starts after sessions are reconstructed.
• Time Series Analysis with SQL: A Practical Guide for the broader temporal-analysis layer around event data.
• Solving the Gaps and Islands Problem in SQL for another sequence-analysis pattern built on ordered-row boundaries.