Group a stream of user events into sessions in SQL (30-minute inactivity gap) using window functions.

TL;DR: Sessionize with two window passes: use LAG to find the gap since each user's previous event, flag a new session when that gap exceeds the threshold (or it is the user's first event), then take a running SUM of those flags per user to assign a session number. This is the gaps-and-islands pattern, O(n log n) from the sort, far better than a self-join.

How to approach it. State the rule (a new session starts after 30 minutes of inactivity) and name it: this is a gaps-and-islands problem. Outline the two passes (flag boundaries with LAG, then cumulative-sum them into session ids) before writing it, and call out that everything partitions by user.

A strong answer.

WITH flagged AS (
  SELECT
    user_id,
    event_time,
    -- 1 when this event begins a new session, else 0
    CASE
      WHEN event_time - LAG(event_time) OVER (
             PARTITION BY user_id ORDER BY event_time
           ) > INTERVAL '30 minutes'
        OR LAG(event_time) OVER (
             PARTITION BY user_id ORDER BY event_time
           ) IS NULL                       -- user's first event
      THEN 1 ELSE 0
    END AS is_new_session
  FROM events
)
SELECT
  user_id,
  event_time,
  SUM(is_new_session) OVER (
    PARTITION BY user_id ORDER BY event_time
    ROWS UNBOUNDED PRECEDING
  ) AS session_id                          -- running count = session number
FROM flagged;

The pattern: LAG gives the previous event's time per user, so the gap is a subtraction; a gap over the threshold (or a NULL previous, meaning the first event) marks a session boundary as 1. A running SUM of those 1s assigns a monotonically increasing session id within each user. Both passes share the same ordered window, so the engine sorts each partition once. Wrap (user_id, session_id) to count sessions, compute session length, or events per session. The mistake that gets people cut is a correlated self-join to find "the most recent earlier event," which is O(n^2) and dies on real traffic.

Key takeaways

• Two ordered window passes, not a join: LAG flags boundaries, running SUM numbers the islands.
• The boundary is gap > threshold OR previous IS NULL; forgetting the NULL drops every user's first session.
• PARTITION BY user_id is mandatory, or events bleed across users into one session.
• Add a tiebreaker to ORDER BY so tied timestamps produce deterministic ids.

What interviewers probe next.

• "Make the session id globally unique, not per-user." Concatenate user_id with the session number, or hash (user_id, session_start_time).
• "Session duration and event count?" Group by (user_id, session_id) and take MAX(event_time) - MIN(event_time) and COUNT(*).
• "Ties on event_time?" Add a tiebreaker (event id) to the ORDER BY so the window is deterministic.
• "Do this in Spark at scale?" Same logic with window functions; watch for skew when a few power users hold most events, and pick a partition key that distributes evenly.

Common mistakes.

• A self-join to find the previous event, which is quadratic and will not scale.
• Forgetting the first-event case (LAG is NULL), so the first session never starts.
• Omitting PARTITION BY user_id, leaking one user's events into another's session.
• A non-deterministic ORDER BY when timestamps tie, giving unstable session ids.