Оконные функции: вопросы для собеседования (часть 4)
Оконные функции — один из самых частых вопросов на собеседовании аналитика. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER — всё это нужно знать наизусть. Интервьюеры проверяют, умеете ли вы решать задачи без подзапросов и самосоединений.
Вопросы 16–20 из 20
16Вы ищете последнюю транзакцию пользователя, используя `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY paid_at DESC)` и оставляя `row_number = 1`. Что верно, если у пользователя две транзакции с одинаковым `paid_at`?
AОбе строки получат `row_number = 1` за счёт повтора номера при равных значениях `paid_at` в окне функции
BЗапрос завершится ошибкой выполнения: оконная `ROW_NUMBER` запрещает совпадения значений `paid_at` между строками
CОдна строка получит `row_number = 1`, другая 2, и какая из них окажется первой нестабильно без второго ключа в `ORDER BY`
DОбе строки будут исключены из результата: условие `row_number = 1` для одинаковых `paid_at` отсекает обе строки разом
Ответ: `ROW_NUMBER` всегда уникален, но при совпадении значений в сортировке результат может быть нестабильным без дополнительного ключа в `ORDER BY`.
`ROW_NUMBER()` назначает уникальный номер каждой строке в соответствии с порядком из `ORDER BY` внутри `OVER`. Если сортировочный ключ не уникален (совпадение по `paid_at`), то относительный порядок строк с одинаковым временем не определён, и какая транзакция получит `row_number = 1` — может меняться. Чтобы сделать выбор детерминированным, добавляют дополнительный ключ, например `ORDER BY paid_at DESC, transaction_id DESC`.
Подробный разбор → 17В таблице с месячной выручкой (по одной строке на пользователя и месяц) нужно посчитать изменение относительно прошлого месяца: текущая `revenue` минус прошлая `revenue`. Какое выражение корректно?
A`revenue - LEAD(revenue) OVER (PARTITION BY user_id ORDER BY month)`
B`revenue - LAG(revenue) OVER (PARTITION BY user_id)`
C`revenue - LAG(revenue) OVER (PARTITION BY user_id ORDER BY month)`
D`revenue - SUM(revenue) OVER (PARTITION BY user_id ORDER BY month)`
Ответ: Для сравнения «текущий период минус прошлый» нужна `LAG` с сортировкой по времени.
`LAG(revenue) OVER (PARTITION BY user_id ORDER BY month)` возвращает значение `revenue` из предыдущего месяца для того же `user_id`. Тогда разница `revenue - LAG(revenue) ...` даёт изменение от прошлого периода к текущему. Без сортировки по `month` понятие «предыдущий месяц» не определено. С `LEAD` вы сравните текущий месяц со следующим, то есть получите сдвиг вперёд, а `SUM(...) OVER` посчитает накопленный итог, а не разницу.
Подробный разбор → 18Вы хотите добавить колонку «номер покупки пользователя за всю историю» через `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_at)`, но в том же запросе ограничиваете данные `WHERE order_at >= '2025-12-01'`. Какой эффект это окажет на нумерацию?
AНумерация будет считаться по всей истории заказов пользователя независимо от условия `WHERE` по дате `order_at`
BЗапрос завершится с ошибкой: оконные функции `ROW_NUMBER` запрещено комбинировать с условием `WHERE` в одном уровне
CНумерация посчитается только по отфильтрованным строкам; для номера по всей истории `ROW_NUMBER` считают в подзапросе или CTE
DДостаточно заменить `ROW_NUMBER` на `DENSE_RANK`, и нумерация автоматически станет «по всей истории» c учётом всех заказов
Ответ: Оконные функции считаются после `WHERE`, поэтому фильтр меняет набор строк, по которым работает окно.
В логическом порядке выполнения SQL сначала применяются `FROM` и `WHERE`, и только потом вычисляются выражения `SELECT`, включая оконные функции. Поэтому `WHERE order_at >= '2025-12-01'` отбрасывает более ранние покупки, и `ROW_NUMBER()` начинает нумерацию заново на оставшемся наборе. Если нужен номер покупки по всей истории, посчитайте `ROW_NUMBER()` в подзапросе или CTE без фильтра, а внешний запрос уже фильтруйте по дате. Замена на `DENSE_RANK` логику не меняет, и оконные функции вполне совместимы с `WHERE`.
Подробный разбор → 19Для каждого дня нужно вывести пользователей, попавших в топ-3 мест по выручке за день. Правило мест такое: при тайах места пропускаются (например, 1, 1, 3, 4...). Какое условие на оконную функцию корректно для отбора?
AОставить строки, где `ROW_NUMBER() OVER (PARTITION BY dt ORDER BY revenue DESC) <= 3`
BОставить строки, где `DENSE_RANK() OVER (PARTITION BY dt ORDER BY revenue DESC) <= 3`
CОставить строки, где `SUM(revenue) OVER (PARTITION BY dt ORDER BY revenue DESC) <= 3`
DОставить строки, где `RANK() OVER (PARTITION BY dt ORDER BY revenue DESC) <= 3`
Ответ: Для «мест с пропусками» используйте `RANK` и фильтр по рангу.
`RANK()` реализует «соревновательное» ранжирование: одинаковые значения делят место, а следующее место сдвигается (1, 1, 3...). Поэтому для отбора топ-3 мест логично посчитать `RANK() OVER (PARTITION BY dt ORDER BY revenue DESC)` и оставить строки с `rank <= 3`. `ROW_NUMBER()` может отрезать часть тайа, а `DENSE_RANK()` считает места без пропусков (1, 1, 2...), что не соответствует заданному правилу.
Подробный разбор → 20У пользователя бывают несколько событий с одинаковым `event_time` (например, батч-логирование). Вы используете `LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time)` чтобы получить предыдущее событие. Как сделать результат стабильным при совпадениях по времени?
AДобавить `PARTITION BY event_time` в окно, чтобы строки с одинаковым временем оказались в разных секциях
BДобавить тай-брейкер `event_id` в `ORDER BY event_time, event_id`, и порядок станет детерминированным
CЗаменить `LAG` на оконную `RANK` и брать строки с `rank = 2` для устойчивой обработки совпадений
DУбрать `ORDER BY event_time` из окна, тогда порядок определится естественным порядком вставки строк
Ответ: Если `ORDER BY` не уникален, добавьте тай-брейкер (уникальный ключ строки) в сортировку оконной функции.
`LAG` определяет «предыдущую строку» только через порядок `ORDER BY` внутри `OVER`. Если `event_time` не уникален, порядок строк с одинаковым временем может быть произвольным, и тогда «предыдущее событие» будет нестабильным. Решение — сделать сортировку детерминированной: добавить уникальный ключ события как тай-брейкер, например `ORDER BY event_time, event_id`.
Подробный разбор →