How would you use the LAG, WHEN, and CUMSUM functions to identify user session boundaries based on a 30‑minute inactivity gap?

To segment user activity into sessions, you can compute the time difference between each row and the previous row for the same user, flag a new session when the gap exceeds 30 minutes, and then use a cumulative sum to assign a session ID. In SQL:

WITH ordered AS (
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM activity
),
flags AS (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN prev_time IS NULL OR TIMESTAMPDIFF(MINUTE, prev_time, event_time) > 30
      THEN 1 ELSE 0 END AS new_session
  FROM ordered
),
sessions AS (
  SELECT
    user_id,
    event_time,
    SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) AS session_id
  FROM flags
)
SELECT * FROM sessions;

The LAG function fetches the previous timestamp. The CASE expression marks a new session when the gap is >30 minutes or when there is no previous row. CUMSUM (implemented as a windowed SUM) aggregates these flags to produce a monotonically increasing session ID per user. Complexity is O(n) with a single scan per user, and the query is efficient on indexed timestamp columns.