Оконные функции: вопросы для собеседования (часть 2)
Оконные функции — один из самых частых вопросов на собеседовании аналитика. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER — всё это нужно знать наизусть. Интервьюеры проверяют, умеете ли вы решать задачи без подзапросов и самосоединений.
Вопросы 6–10 из 20
6В таблице метрик есть `dt`, `platform`, `dau`. Нужно вывести значение `dau` за предыдущий день для той же платформы, чтобы посчитать дневное изменение. Какое выражение верное?
A`LEAD(dau) OVER (PARTITION BY platform ORDER BY dt)`
B`LAG(dau) OVER (PARTITION BY platform)`
C`LAG(dau) OVER (ORDER BY dt)`
D`LAG(dau) OVER (PARTITION BY platform ORDER BY dt)`
Ответ: `LAG` берет предыдущее значение по `ORDER BY` внутри каждой `PARTITION BY`.
Чтобы получить «вчерашний `dau`», нужно определить порядок по времени через `ORDER BY dt`. Чтобы сравнение шло внутри одной платформы, нужен `PARTITION BY platform`. Поэтому корректно: `LAG(dau) OVER (PARTITION BY platform ORDER BY dt)`. Без `ORDER BY` понятие «предыдущий день» не определено, а без `PARTITION BY` вы будете сравнивать разные платформы между собой.
7Хотите добавить к каждой транзакции колонку «доля от общей суммы транзакций этого пользователя» и при этом не терять детализацию по транзакциям. Какой фрагмент корректен?
A`amount / SUM(amount)`
B`amount / SUM(amount) OVER (PARTITION BY user_id)`
C`amount / SUM(amount) OVER (ORDER BY paid_at)`
D`amount / SUM(amount) GROUP BY user_id`
Ответ: Оконная агрегация считает итог по группе и возвращает его в каждой строке, сохраняя гранулярность.
Фрагмент `amount / SUM(amount) OVER (PARTITION BY user_id)` считает общий `SUM(amount)` по каждому `user_id` и делит сумму конкретной транзакции на этот общий итог. Так сохраняются все строки транзакций, что важно для анализа вкладов/долей. Если использовать `GROUP BY user_id`, вы схлопнете данные до одной строки на пользователя и потеряете детализацию по транзакциям.
8В таблице платежей нужно вывести для каждой транзакции накопительную сумму платежей пользователя на этот момент (running total). Какое выражение даст накопительный итог по пользователю?
A`SUM(amount) OVER (PARTITION BY user_id)`
B`SUM(amount)` с `GROUP BY user_id`
C`SUM(amount) OVER (PARTITION BY user_id ORDER BY paid_at)`
D`SUM(amount) OVER (ORDER BY paid_at)`
Ответ: Накопительный итог требует `ORDER BY` в окне, а раздельность по пользователям — `PARTITION BY`.
`SUM(amount) OVER (PARTITION BY user_id ORDER BY paid_at)` суммирует `amount` внутри каждого `user_id` в порядке `paid_at`, возвращая кумулятивный итог в каждой строке. Без `ORDER BY` получится общий итог по пользователю, повторенный в каждой строке. Без `PARTITION BY` накопление будет считаться по всем пользователям вместе, что ломает пользовательскую аналитику.
9Вы хотите получить «предыдущую цену» товара по дням и пишете `LAG(price) OVER (PARTITION BY product_id)`. Почему результат может быть неожиданным?
A`LAG` всегда возвращает `NULL`, если в окне нет `ORDER BY`
BБез `ORDER BY` понятие «предыдущая строка» в окне не определено, поэтому «предыдущее» значение может быть произвольным
CНужно заменить `LAG` на `RANK`, потому что `LAG` не работает по товарам
DНужно добавить `GROUP BY product_id`, иначе `LAG` запрещен
Ответ: Для `LAG` обязателен осмысленный порядок через `ORDER BY` в окне.
Окно `OVER (PARTITION BY product_id)` задает группу строк товара, но не задает их порядок. Без `ORDER BY dt` невозможно определить, какая строка «вчера», поэтому `LAG(price)` может вернуть не то значение, которое вы ожидаете во временном ряду. Чтобы анализировать динамику, используйте `LAG(price) OVER (PARTITION BY product_id ORDER BY dt)` (и при совпадениях добавьте тай-брейкер).
10Вы делаете рейтинг товаров по выручке внутри категории. Если два товара делят 2 место, следующий товар должен получить 3 место (без пропуска). Какая функция подходит лучше всего?
A`DENSE_RANK`
B`RANK`
C`ROW_NUMBER`
D`LEAD`
Ответ: `DENSE_RANK` присваивает одинаковое место равным значениям и не делает пропусков в нумерации.
`DENSE_RANK()` дает одинаковый ранг равным значениям, а следующий ранг увеличивает на 1: 1, 2, 2, 3... Это подходит, когда места должны идти без пропусков. В `RANK()` после равенства появляются пропуски (1, 2, 2, 4...). `ROW_NUMBER()` вообще не выделяет одинаковые места: у каждой строки свой номер.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram