Оконные функции: вопросы для собеседования (часть 3)
Оконные функции — один из самых частых вопросов на собеседовании аналитика. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER — всё это нужно знать наизусть. Интервьюеры проверяют, умеете ли вы решать задачи без подзапросов и самосоединений.
Вопросы 11–15 из 20
11В одной категории 4 товара с выручкой 100, 100, 90 и 80. Вычисляем `RANK() OVER (ORDER BY revenue DESC)`. Какие ранги получат строки при сортировке по убыванию выручки?
A`1, 1, 3, 4`
B`1, 1, 2, 3`
C`1, 2, 3, 4`
D`1, 1, 2, 4`
Ответ: `RANK` присваивает одинаковый ранг равным значениям и делает пропуски в нумерации после равенства.
При `RANK() OVER (ORDER BY revenue DESC)` одинаковые значения выручки получают один ранг, а следующий после равенства увеличивается на количество строк в группе. Поэтому 100 и 100 получают ранг 1, а 90 — уже ранг 3 (а не 2). Для сравнения: `ROW_NUMBER()` всегда даёт уникальные номера 1, 2, 3, 4, а `DENSE_RANK()` не делает пропусков — 1, 1, 2, 3.
Подробный разбор → 12Что именно посчитает выражение `SUM(amount) OVER (PARTITION BY campaign_id ORDER BY event_time)` в таблице платежей по кампаниям?
AОдин общий накопительный итог `SUM(amount)` по всем кампаниям сразу, упорядоченный по `event_time` без разбиения по `campaign_id`
BПо одной строке на каждое значение `campaign_id` с итоговым `SUM(amount)` без учёта порядка `event_time` и без накопления
CСначала отсортирует все строки по `event_time`, а затем разделит на группы по `campaign_id` и пересчитает `SUM(amount)` заново
DНакопительный итог `SUM(amount)` отдельно внутри каждой группы `campaign_id`, в порядке `event_time` для каждого окна
Ответ: `PARTITION BY` задаёт независимые окна по группам, а `ORDER BY` — порядок накопления внутри каждой группы.
Окно разбивается по `campaign_id`, поэтому суммы считаются независимо для каждой кампании. Внутри каждой кампании строки упорядочиваются по `event_time`, и `SUM(amount)` даёт кумулятивный итог на каждой строке. Это полезно для построения накопительных графиков расходов или выручки по кампаниям, не теряя детализацию по событиям.
Подробный разбор → 13Нужно выбрать ровно один «самый дорогой» заказ каждого пользователя. Если у пользователя несколько заказов с одинаковой суммой, нужно взять самый поздний по времени. Какое окно решает задачу?
A`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC)`
B`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, order_at DESC)`
C`RANK() OVER (PARTITION BY user_id ORDER BY amount DESC, order_at DESC)`
D`DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC, order_at DESC)`
Ответ: Чтобы детерминированно разрешить равенство значений, добавьте второй ключ в `ORDER BY` окна.
`ROW_NUMBER()` присваивает уникальные номера и позволяет потом отфильтровать ровно одну строку с номером 1 на каждого пользователя. Чтобы при равной сумме всегда выбирать самый поздний заказ, в порядок добавляют второй ключ: сначала сумма по убыванию, затем дата заказа по убыванию. `RANK()` и `DENSE_RANK()` могут дать одинаковый ранг нескольким строкам и оставят их все, поэтому одной строкой результат не ограничится. Если в `ORDER BY` оставить только сумму, при равных суммах СУБД выберет строку недетерминированно.
Подробный разбор → 14В недельной витрине метрик вы хотите вывести значение выручки из строки «две недели назад» отдельно для каждого продукта. Какой вызов оконной функции корректен?
A`LAG(revenue) OVER (PARTITION BY product_id ORDER BY week)`
B`LEAD(revenue, 2) OVER (PARTITION BY product_id ORDER BY week)`
C`LAG(revenue, 2) OVER (PARTITION BY product_id ORDER BY week)`
D`FIRST_VALUE(revenue) OVER (PARTITION BY product_id ORDER BY week)`
Ответ: Второй аргумент `LAG(x, n)` задаёт смещение на n строк назад внутри секции окна.
Вызов `LAG(revenue, 2) OVER (PARTITION BY product_id ORDER BY week)` возвращает значение выручки из строки, которая находится на две позиции раньше в порядке `week` внутри секции `product_id`. Это удобно для сравнений с лагом 2 (например, текущая неделя против двух недель назад), когда сдвиг на одну неделю недостаточно информативен. Вариант `LEAD` смотрит вперёд, `LAG` без второго аргумента — на одну строку назад, а `FIRST_VALUE` всегда отдаёт первую строку секции.
Подробный разбор → 15В каждой категории нужно выбрать товары с тремя наибольшими различными значениями выручки (`revenue`), включая все товары, которые делят эти значения при равенстве. Какой вариант даст нужный результат?
AПосчитать `DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC)` и оставить строки, где `dense_rank <= 3`
BПосчитать `RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC)` и оставить строки, где `rank <= 3`
CСделать `GROUP BY category_id` и взять три категории с наибольшей суммарной выручкой через `LIMIT 3`
DПосчитать `ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC)` и оставить строки, где `row_number <= 3`
Ответ: Для выбора первых N различных значений с учётом равных значений удобнее `DENSE_RANK`.
Если нужно взять именно три различных уровня выручки внутри категории и включить все равные значения, подходит `DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC)`: одинаковые значения делят один ранг, а ранги идут без пропусков. Условие `dense_rank <= 3` захватит ровно три уровня значений. `ROW_NUMBER` ограничит до трёх строк и может «отрезать» часть строк с равной выручкой. `RANK` пропустит ранг 3 при равных значениях сверху, и тогда третий уровень по значению не попадёт в выборку. `GROUP BY` + `LIMIT 3` агрегирует категории, а не товары внутри.
Подробный разбор →