🏋️ SQL Gym Pro 7 - разбор

Многие застревают на этой задаче, потому что пытаются решить её через GROUP BY. Но сессия - это не фиксированное окно, а состояние, которое длится, пока паузы между действиями коротки.

Разберем решение:

Шаг 1: Ищем «соседа» с помощью LAG Нам нужно сравнить время текущего события со временем предыдущего. SQL не видит строки одновременно, поэтому мы «подтягиваем» значение из прошлой строки в текущую.

Шаг 2: Ставим метку разрыва (Flag) Теперь мы сравниваем utc_created_dttm и prev_dttm.Если разница $> 15$ минут или это вообще первое событие пользователя (NULL) — значит, это начало новой сессии.Ставим 1 (новый старт) или 0 (продолжение).

Шаг 3: Кумулятивная сумма Это самый важный этап. Если мы просто просуммируем наши единички и нолики от начала до текущей строки, мы получим порядковый номер сессии.

-- Полное решение задачи WITH -- Шаг 1: Достаем время предыдущего события для каждого пользователя step1_lag AS ( SELECT user_id, event_name, utc_event_dttm, LAG(utc_event_dttm) OVER ( PARTITION BY user_id ORDER BY utc_event_dttm ) AS prev_event_dttm FROM user_logs ),

-- Шаг 2: Определяем границы. Если пауза > 15 мин — это флаг новой сессии (1) step2_flags AS ( SELECT *, CASE WHEN prev_event_dttm IS NULL THEN 1 -- Самое первое событие пользователя WHEN utc_event_dttm > prev_event_dttm + INTERVAL '15 minutes' THEN 1 ELSE 0 END AS is_new_session FROM step1_lag ),

-- Шаг 3: Считаем порядковый номер сессии через кумулятивную сумму флагов step3_session_num AS ( SELECT user_id, event_name, utc_event_dttm, SUM(is_new_session) OVER ( PARTITION BY user_id ORDER BY utc_event_dttm ) AS session_number FROM step2_flags )

-- Финальный шаг: Формируем итоговый ID сессии SELECT user_id, event_name, utc_event_dttm, -- Конкатенируем ID пользователя и номер, чтобы ID был уникален во всей таблице CONCAT(user_id, '_', session_number) AS session_id FROM step3_session_num ORDER BY user_id, utc_event_dttm;