Сессионизация кликстрима по разрыву 30 минут

Условие задачи

В таблице pageviews — просмотры страниц пользователей с меткой времени event_time ('YYYY-MM-DD HH:MM:SS'). Нужно разбить активность каждого пользователя на сессии по правилу: если между соседними (по времени) событиями пользователя прошло СТРОГО БОЛЬШЕ 30 минут — начинается новая сессия. Первое событие пользователя всегда открывает сессию. Верните число сессий на пользователя: колонки user_id и session_count, отсортированные по user_id. Используйте оконные функции (LAG для предыдущего события, кумулятивную сумму флагов новой сессии).

Схема данных

CREATE TABLE pageviews (
  user_id    INTEGER,
  event_time TEXT   -- метка времени 'YYYY-MM-DD HH:MM:SS'
);

Темы

sessionization lag window-functions julianday cumulative-sum gap-and-island

Подсказки

• Первый CTE: через LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) достаньте время предыдущего события того же пользователя. Разницу в минутах в SQLite считают как (julianday(cur) - julianday(prev)) * 24 * 60 — julianday возвращает дни, а не секунды.
• Флаг новой сессии = 1, если предыдущего события нет (LAG вернул NULL, это первое событие пользователя) ИЛИ разрыв строго больше 30 минут; иначе 0. Порог именно '> 30', так что ровно 30 минут — та же сессия.
• Второй CTE: кумулятивная сумма флагов SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) даёт номер сессии внутри пользователя. Затем COUNT(DISTINCT session_id) с GROUP BY user_id — число сессий.

Что проверяет эта задача

Задача «Сессионизация кликстрима по разрыву 30 минут» учит решать задачи по теме «Сессионизация» так, как это спрашивают на собеседовании аналитика данных. Такие задачи встречаются на собеседованиях в Yandex и других IT-компаниях. Уровень сложный — задача проверяет глубину и умение собрать запрос из нескольких приёмов.

Как подступиться к решению

Сначала разберись со схемой данных и пойми, какой результат нужен на выходе. Определи, какие таблицы и поля задействованы, и собирай запрос пошагово: фильтрация → соединение или группировка → итоговый отбор и сортировка. Проверь результат на крайних случаях — NULL, дубликаты, пустые группы. Свой запрос сразу прогоняешь автопроверкой в интерактивной SQL-песочнице на реальной базе (SQLite и PostgreSQL 16 прямо в браузере).

Хочешь разобрать SQL системно с нуля — пройди бесплатный курс «SQL с нуля» и закрепи навык в тренажёре.

Решить в интерактивной SQL-песочнице → ← Все SQL-задачи