Data Problem Solving and Business Context Questions

SELECT
  user_id,
  occurred_at,
  SUM(CASE WHEN occurred_at - LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) > INTERVAL '30 minutes' THEN 1 ELSE 0 END)
    OVER (PARTITION BY user_id ORDER BY occurred_at) AS session_index
FROM events;

WITH raw_events AS (
  -- Replace this with your table
  SELECT user_id, occurred_at, event_id
  FROM `project.dataset.events`
),

deduped AS (
  -- Remove exact duplicate rows (same user, event_id, timestamp).
  -- If you have an event_uuid use that instead.
  SELECT user_id, occurred_at, event_id
  FROM (
    SELECT
      user_id,
      occurred_at,
      event_id,
      ROW_NUMBER() OVER (PARTITION BY user_id, event_id, occurred_at ORDER BY event_id) AS rn
    FROM raw_events
  )
  WHERE rn = 1
),

ordered AS (
  -- Order per user and compute gap from previous event (in seconds)
  SELECT
    user_id,
    occurred_at,
    event_id,
    LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id) AS prev_occurred_at
  FROM deduped
),

flagged AS (
  SELECT
    user_id,
    occurred_at,
    event_id,
    -- compute gap in seconds; NULL for first event
    IFNULL(TIMESTAMP_DIFF(occurred_at, prev_occurred_at, SECOND), 0) AS gap_seconds,
    -- mark new session if gap > 30 minutes (1800 seconds) or first event (gap = 0 and prev is NULL)
    CASE
      WHEN prev_occurred_at IS NULL THEN 1
      WHEN TIMESTAMP_DIFF(occurred_at, prev_occurred_at, SECOND) > 1800 THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered
),

sessionized AS (
  -- cumulative sum of is_new_session gives session_number per user
  SELECT
    user_id,
    occurred_at,
    event_id,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_number
  FROM flagged
),

with_session_id AS (
  -- create a readable session_id (could be UUID or hash instead)
  SELECT
    user_id,
    session_number,
    occurred_at,
    event_id,
    CONCAT(CAST(user_id AS STRING), "-", CAST(session_number AS STRING)) AS session_id
  FROM sessionized
)

-- final aggregation: session length (seconds) and events per session
SELECT
  user_id,
  session_id,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  TIMESTAMP_DIFF(MAX(occurred_at), MIN(occurred_at), SECOND) AS session_length_seconds,
  COUNT(*) AS events_per_session
FROM with_session_id
GROUP BY user_id, session_id
ORDER BY user_id, session_start;