Дима SQL-ит 🧑‍💻 (Аналитика данных, AI): “🏦 Разбор задачи с собеседования в Тинькофф (Т-банк). Формируем сессии…”

🏦 Разбор задачи с собеседования в Тинькофф (Т-банк).
Формируем сессии для пользователей по указанному интервалу времени в SQL. Часть 2

В предыдущем посте мы просто нашли разрывы неактивности. Сейчас рассмотрим продолжение «Сгруппируй все действия в сессии активности».

Давайте разберем это на примере с двумя пользователями (Вася и Петя) по шагам.

Исходные данные (6 строк логов):

| user_id | action_time | Комментарий 
|---------|-------------|-------------
| Вася    | 10:00       | Старт Васи (Сессия 1) 
| Вася    | 10:15       | Вася активен 
| Вася    | 11:30       | Разрыв! (75 мин) → Старт Васи (Сессия 2)
| Вася    | 11:40       | Вася активен 
| Петя    | 12:00       | Старт Пети (Сессия 1)
| Петя    | 12:10       | Петя активен

Шаг 1. Находим моменты «разрыва»:

С помощью LAG мы смотрим разницу с предыдущим временем внутри каждого юзера. Если разница > 30 минут — ставим флаг 1.

| user_id | action_time | Diff   | is_new_session        |
|---------|-------------|--------|------------------------|
| Вася    | 10:00       | -      | 0                      |
| Вася    | 10:15       | 15 мин | 0                      |
| Вася    | 11:30       | 75 мин | 1 (Новый остров)       |
| Вася    | 11:40       | 10 мин | 0                      |
| Петя    | 12:00       | -      | 0 (Сброс счетчика)     |
| Петя    | 12:10       | 10 мин | 0                      |

WITH step1 AS (
  SELECT 
    user_id,
    action_timestamp,
    CASE 
      WHEN action_timestamp - LAG(action_timestamp) OVER(PARTITION BY user_id ORDER BY action_timestamp) > interval '30 minutes' 
      THEN 1 ELSE 0 
    END as is_new_session
  FROM user_actions
),

Шаг 2. Хитрый трюк с кумулятивной суммой:

Нам нужно дать строчкам одной сессии общий ID. Мы суммируем наши флаги сверху вниз. Благодаря PARTITION BY user_id сумма сбрасывается для нового юзера.

| user_id | action_time | is_new_session | session_id (Сумма)        |
|---------|-------------|----------------|---------------------------|
| Вася    | 10:00       | 0              | 0                         |
| Вася    | 10:15       | 0              | 0                         |
| Вася    | 11:30       | 1              | 1 (Сумма стала 1)         |
| Вася    | 11:40       | 0              | 1                         |
| Петя    | 12:00       | 0              | 0 (Счетчик обнулился!)    |
| Петя    | 12:10       | 0              | 0                         |

step2 AS (
  SELECT 
    *,
    SUM(is_new_session) OVER(PARTITION BY user_id ORDER BY action_timestamp) as session_id
  FROM step1
)

Шаг 3. Финальная агрегация (и как сделать ID уникальным):

Теперь всё просто: мы говорим базе «Сгруппируй все действия с одинаковым user_id и session_id в одну строку». Чтобы ID был глобально уникальным, мы склеиваем их.

| user_id | unique_session_id | start  | end    | count |
|---------|-------------------|--------|--------|-------|
| Вася    | Вася_0            | 10:00  | 10:15  | 2     |
| Вася    | Вася_1            | 11:30  | 11:40  | 2     |
| Петя    | Петя_0            | 12:00  | 12:10  | 2     |

SELECT 
  user_id,
  -- Генерируем уникальный ID для сессии
  user_id || '_' || session_id as unique_session_id,
  MIN(action_timestamp) as session_start,
  MAX(action_timestamp) as session_end,
  COUNT(*) as actions_count
FROM step2
GROUP BY user_id, session_id

Давайте так: 👇
Вы накидываете интересные вам задачи в комментарии и я постараюсь разбирать некоторые из них в следующих постах. Ну и давайте наберем под этим постом 50 реакций ❤️ и я чуть позже приложу в комментариях этот пост в формате markdown для вашего удобства.

Итог: 🤩

Сохраняйте этот пример, чтобы не запутаться в логике!

❓ Вам, попадалась такая задача на собеседованиях? Пишите в комментариях!
✔️ Подпишитесь на канал, чтобы не пропустить следующие хаки.

🚬 Провожу обучение и консультации: mentor.dima-sqlit.ru

@dima_sqlit