StrataScratch (Google) — сессионизация по 30-минутному gap

Условие

Таблица events(user_id, event_time, event_type) — лог событий. Объедините события в сессии: новая сессия начинается, если интервал между событиями пользователя > 30 минут. Верните для каждой сессии:

• user_id,
• session_id (sequential per user),
• session_start, session_end,
• event_count.

Решение

Идея — LAG + накопительная сумма флагов

WITH lagged AS (
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM events
),
flagged AS (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN prev_time IS NULL THEN 1
      WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM lagged
),
sessioned AS (
  SELECT
    user_id,
    event_time,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM flagged
)
SELECT
  user_id, session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  COUNT(*) AS event_count
FROM sessioned
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

Объяснение

1. LAG даёт предыдущее время того же пользователя.
2. Флаг is_new_session = 1, если разрыв > 30 мин (или это первая запись).
3. SUM(is_new_session) OVER (ORDER BY event_time) — накопительный счётчик = id сессии.
4. GROUP BY (user_id, session_id) даёт границы и счётчик событий.

Подводные камни

1. 30 минут — > или >=. В бизнес-логике часто строгое > (ровно 30 мин — всё ещё одна сессия). Уточняйте.
2. event_time с дублями. Два события в одну миллисекунду → LAG даст нулевую разность, обе попадут в одну сессию (это норм).
3. Часовой пояс в event_time. Если TIMESTAMP WITH TZ — разность интервала корректна. Если WITHOUT TZ и пользователи в разных tz — проблемы с сессиями вокруг полуночи.
4. Производительность. На сотнях млн строк сессионизация дорогая. В Spark/BigQuery — те же оконные функции; для жирных датасетов помогает партиционирование по дате.

Эталонный ответ

LAG + флаг новой сессии + накопительный SUM = session_id per user. Затем GROUP BY (user_id, session_id). Самый широкий аналитический паттерн.