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

🏦 Разбор задачи с собеседования в Тинькофф (Т-банк).
Считаем «окна» неактивности пользователя в SQL. Часть 1

На интервью в крупные финтех-компании (Тинькофф, Яндекс, Авито) часто дают эту задачу, чтобы проверить, умеете ли вы работать с временными рядами и оконными функциями.

Суть задачи:
Есть таблица логов user_actions с колонками user_id и action_timestamp. Нужно найти все случаи, когда пользователь «пропадал» более чем на 30 минут между двумя действиями.

Как это решается «на пальцах»?
Нам нужно сравнить время текущего действия со временем ПРЕДЫДУЩЕГО. В SQL для этого есть идеальный инструмент — оконная функция LAG. Представьте, что вы стоите в очереди и спрашиваете человека сзади: «А когда зашел тот, кто был перед тобой?».

Простейший пример решения:

WITH time_diffs AS (
  SELECT 
    user_id,
    action_timestamp,
    -- Достаем время предыдущего действия для этого же юзера
    LAG(action_timestamp) OVER(PARTITION BY user_id ORDER BY action_timestamp) as prev_ts
  FROM user_actions
)
SELECT 
  user_id,
  prev_ts as gap_start,
  action_timestamp as gap_end,
  -- Считаем разницу (в разных СУБД синтаксис даты чуть разный)
  (action_timestamp - prev_ts) as duration
FROM time_diffs
WHERE (action_timestamp - prev_ts) > interval '30 minutes'

Почему здесь нужны оконные функции?
1️⃣ PARTITION BY user_id: Мы не хотим сравнивать время Васи со временем Пети. Окно «запирает» расчеты внутри конкретного пользователя.
2️⃣ ORDER BY: Чтобы LAG понимал, какое действие было действительно предыдущим, данные должны быть отсортированы по времени.
3️⃣ Чистота: Без оконных функций пришлось бы делать тяжелый JOIN таблицы самой на себя, что на миллионах строк просто «повесит» базу.

Давайте так: 👇
Эта задача — только верхушка айсберга. Если мы наберем под этим постом 50 реакций ❤️, я выложу вторую часть решения: как не просто найти разрывы, а сгруппировать их в полноценные сессии неактивности (задача Gaps and Islands в переводе "Пробелы и Острова").

Итог: 🤩

Оконные функции — это маст-хэв для аналитика. Научитесь пользоваться LAG и LEAD, и 80% задач на временные интервалы будут решаться за 5 минут.

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

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

@dima_sqlit