Оконные функции: вопросы для собеседования (часть 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)`. Какие ранги будут у строк, если упорядочить товары по убыванию выручки?
A1, 2, 3, 4
B1, 1, 3, 4
C1, 1, 2, 3
D1, 1, 2, 4
Ответ: `RANK` присваивает одинаковый ранг равным значениям и делает пропуски в нумерации после равенства.
При `RANK() OVER (ORDER BY revenue DESC)` одинаковые значения `revenue` получают один и тот же ранг. Следующий после равенства ранг увеличивается на количество строк в группе равных значений, поэтому появляются пропуски (например, 1, 1, 3, 4). Для сравнения: `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Один общий накопительный итог по всем кампаниям, упорядоченный по `event_time`
BНакопительный итог `amount` отдельно внутри каждой `campaign_id`, в порядке `event_time`
CОдну строку на `campaign_id` с итоговым `SUM(amount)`
DСначала отсортирует все строки по `event_time`, а затем разделит на `campaign_id` и пересчитает суммы заново
Ответ: `PARTITION BY` задает независимые окна по группам, а `ORDER BY` — порядок накопления внутри каждой группы.
Окно разбивается по `campaign_id`, поэтому суммы считаются независимо для каждой кампании. Внутри каждой кампании строки упорядочиваются по `event_time`, и `SUM(amount)` дает кумулятивный итог на каждой строке. Это полезно для построения накопительных графиков spend/revenue по кампаниям, не теряя детализацию по событиям.
13Нужно выбрать ровно один «самый дорогой» заказ пользователя. Если несколько заказов с одинаковым `amount`, выбрать самый поздний по `order_at`. Какой `ORDER BY` внутри `ROW_NUMBER() OVER (...)` решает задачу?
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)`
D`DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC)`
Ответ: Чтобы детерминированно разрешить равенство значений, добавьте второй ключ в `ORDER BY` окна.
`ROW_NUMBER()` выбирает ровно одну строку с номером 1. Чтобы при равном `amount` всегда выбирать самый поздний заказ, порядок должен сначала сортировать по `amount DESC`, а затем по `order_at DESC`: `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, order_at DESC)`. Если оставить только `amount DESC`, то при равном `amount` выбор строки может быть нестабильным.
14В недельной витрине метрик вы хотите вывести значение `revenue` из строки «две недели назад» для каждого продукта. Какой вызов корректен?
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`DENSE_RANK(revenue, 2) OVER (PARTITION BY product_id ORDER BY week)`
Ответ: Второй аргумент `LAG(x, n)` задает смещение на n строк назад.
`LAG(revenue, 2) OVER (PARTITION BY product_id ORDER BY week)` вернет `revenue` из строки, которая находится на две позиции раньше в порядке `week` внутри `product_id`. Это удобно для сравнений с лагом 2 (например, текущая неделя vs две недели назад), когда «вчера» недостаточно информативно.
15В каждой категории нужно выбрать товары с тремя наибольшими различными значениями `revenue`, включая все товары, которые делят эти значения (при равных значениях). Какой вариант даст нужный результат?
AПосчитать `RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC)` и оставить строки, где `rank <= 3`
BПосчитать `ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC)` и оставить строки, где `row_number <= 3`
CСделать `GROUP BY category_id` и взять `TOP 3` категории по суммарной `revenue`
DПосчитать `DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC)` и оставить строки, где `dense_rank <= 3`
Ответ: Для выбора top-N различных значений с учетом равных значений удобнее `DENSE_RANK`.
Если нужно взять именно три различных уровня `revenue` внутри категории и включить все равные значения, то подходит `DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC)`: одинаковые значения делят один ранг, а ранги идут без пропусков. Условие `dense_rank <= 3` захватит ровно три уровня значений. `ROW_NUMBER` ограничивает количество строк и может «отрезать» часть строк с равной `revenue`. `RANK` может пропустить ранг 3 при равных значениях сверху, и тогда третий уровень по значению не попадет в выборку.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram