Оконные функции: вопросы для собеседования (часть 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`, потому что время одинаковое
BЗапрос завершится ошибкой: `ROW_NUMBER` нельзя применять при одинаковых `paid_at`
CОдна строка получит `row_number = 1`, другая — `row_number = 2`, но какая именно станет первой может быть нестабильно без доп. тай-брейкера в `ORDER BY`
DОбе строки будут исключены, потому что `row_number = 1` не может быть выбран
Ответ: `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` с `ORDER BY` по времени.
`LAG(revenue) OVER (PARTITION BY user_id ORDER BY month)` возвращает значение `revenue` из предыдущего месяца для того же `user_id`. Тогда разница `revenue - LAG(revenue) ...` дает period-over-period изменение. Без `ORDER BY month` «предыдущий месяц» не определен. С `LEAD` вы сравните текущий месяц со следующим, то есть получите «сдвиг вперед».
18Вы хотите добавить колонку «номер покупки пользователя за всю историю» через `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_at)`, но в том же запросе ограничиваете данные `WHERE order_at >= '2025-12-01'`. Какой эффект это окажет на нумерацию?
AНумерация будет по всей истории, фильтр `WHERE` на нее не влияет
BЗапрос не выполнится: оконные функции нельзя использовать вместе с `WHERE`
CНумерация будет считаться только внутри отфильтрованных строк; для «за всю историю» нужно сначала посчитать `ROW_NUMBER` на полной истории в подзапросе, а фильтровать снаружи
DНужно заменить `ROW_NUMBER` на `DENSE_RANK`, и тогда нумерация станет «за всю историю»
Ответ: Оконные функции считаются после применения `WHERE`, поэтому фильтр меняет набор строк в окне.
В логическом порядке выполнения SQL сначала применяется `FROM` и `WHERE`, а уже потом вычисляются выражения `SELECT`, включая оконные функции. Поэтому `WHERE order_at >= '2025-12-01'` удалит более ранние покупки, и `ROW_NUMBER()` начнет нумерацию заново на оставшемся наборе. Если нужен номер покупки по всей истории, сначала посчитайте `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_at)` в подзапросе/CTE без этого фильтра, а затем примените фильтр во внешнем запросе.
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Убрать `ORDER BY event_time`, тогда «предыдущее» будет определено
CЗаменить `LAG` на `RANK` и брать строки, где `rank = 2`
DДобавить в `ORDER BY` второй ключ-тай-брейкер (например `event_id`) вместе с `event_time`, чтобы порядок был детерминированным
Ответ: Если `ORDER BY` не уникален, добавьте тай-брейкер (уникальный ключ) в сортировку окна.
`LAG` определяет «предыдущую строку» только через порядок `ORDER BY` внутри `OVER`. Если `event_time` не уникален, порядок строк с одинаковым временем может быть произвольным, и тогда «предыдущее событие» будет нестабильным. Решение — сделать сортировку детерминированной: добавить уникальный ключ события как тай-брейкер, например `ORDER BY event_time, event_id`.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram