🏦 Разбор задачи с собеседования в Тинькофф (Т-банк).
Считаем «окна» неактивности пользователя в 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
Считаем «окна» неактивности пользователя в 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