Вы хотите сравнить текущую метрику с метрикой следующего периода во временном ряду. Какая оконная функция возвращает «следующее» значение относительно текущей строки по заданному порядку сортировки?
Оконные функции — один из самых частых вопросов на собеседовании аналитика. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER — всё это нужно знать наизусть. Интервьюеры проверяют, умеете ли вы решать задачи без подзапросов и самосоединений.
Всего в этом разделе 20 вопросов. Каждый — с правильным ответом и кратким разбором теории. Разбито на 4 части по 5 вопросов.
1Вы написали `SUM(amount) OVER (ORDER BY paid_at)` и ожидали накопительную сумму по каждому пользователю, но сумма растёт сквозь всех пользователей. Что нужно добавить в `OVER`, чтобы накопление считалось отдельно по каждому пользователю?
AДобавить `PARTITION BY user_id`, чтобы окно сбрасывалось на границе каждого пользователя
BУбрать `ORDER BY paid_at`, чтобы сумма перестала накапливаться по времени
CДобавить `GROUP BY user_id` рядом с оконной функцией для группировки
DЗаменить `SUM` на `RANK`, чтобы получить накопление по позиции строки
Ответ: `PARTITION BY` разделяет окно на независимые группы строк.
Без `PARTITION BY` окно одно на весь набор строк, поэтому кумулятивная сумма считается глобально. Добавив `PARTITION BY user_id`, вы получите независимую кумулятивную сумму внутри каждого пользователя, а `ORDER BY paid_at` задаст порядок накопления по времени. `GROUP BY` и оконная функция несовместимы в этой логике, а `RANK` считает позиции строк, а не суммы.
2Нужно пронумеровать заказы каждого пользователя по времени покупки, начиная с 1, чтобы найти его 1-й, 2-й, 3-й заказ. Какое выражение подходит?
A`ROW_NUMBER() OVER (ORDER BY order_at)`
B`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_at)`
C`RANK() OVER (PARTITION BY user_id ORDER BY order_at)`
D`SUM(order_id) OVER (PARTITION BY user_id ORDER BY order_at)`
Ответ: `PARTITION BY` сбрасывает счётчик для каждого пользователя, а `ORDER BY` задаёт порядок нумерации.
`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_at)` нумерует строки внутри каждого `user_id` по возрастанию времени покупки. Без `PARTITION BY` номера будут общими на всю таблицу. `RANK()` уместен, когда важны места и допустимы тайы (одинаковое время даст одинаковые ранги). `SUM(...) OVER (...)` считает нарастающий итог, а не порядковый номер.
3Вы хотите сравнить текущую метрику с метрикой следующего периода во временном ряду. Какая оконная функция возвращает «следующее» значение относительно текущей строки по заданному порядку сортировки?
A`LEAD(x)` возвращает значение `x` из следующей строки относительно текущей по порядку окна
B`LAG(x)` возвращает значение `x` из предыдущей строки относительно текущей по порядку окна
C`RANK()` возвращает порядковый ранг строки внутри окна с пропусками номеров при тайах
D`ROW_NUMBER()` возвращает порядковый номер строки внутри окна без пропусков при тайах
Ответ: `LEAD` смотрит вперёд по окну на N строк, а `LAG` — назад; обе зависят от заданного порядка сортировки.
В окне `... OVER (PARTITION BY ... ORDER BY ...)` функция `LEAD(x)` возвращает значение `x` из следующей строки относительно текущей по заданному порядку, а `LAG(x)` — из предыдущей. Это базовый приём для сравнений «период к периоду» вперёд или назад. `RANK` и `ROW_NUMBER` решают другую задачу — нумеруют строки, а не достают значение соседней строки.
4Для каждой покупки пользователя нужно добавить дату следующей покупки этого же пользователя, чтобы потом посчитать интервал между покупками. Что использовать?
A`LAG(order_at) OVER (PARTITION BY user_id ORDER BY order_at)`
B`LEAD(order_at) OVER (ORDER BY order_at)`
C`RANK() OVER (PARTITION BY user_id ORDER BY order_at)`
D`LEAD(order_at) OVER (PARTITION BY user_id ORDER BY order_at)`
Ответ: `LEAD` возвращает значение из следующей строки относительно текущей по порядку `ORDER BY` внутри окна.
Окно `OVER (PARTITION BY user_id ORDER BY order_at)` выстраивает покупки каждого пользователя в хронологическом порядке. `LEAD(order_at)` берёт `order_at` из следующей строки этого окна, что и даёт дату следующей покупки пользователя. `LAG` смотрит назад и вернёт предыдущую покупку, `RANK` возвращает номер строки, а без `PARTITION BY user_id` следующая строка может оказаться покупкой совсем другого пользователя.
5Вы хотите получить одну строку на пользователя с итоговой выручкой и заменили `GROUP BY` на оконную `SUM(amount) OVER (PARTITION BY user_id)`. Почему результат содержит столько же строк, сколько и исходный набор?
AОконная агрегация подставляет итог в каждую строку секции и не меняет гранулярность, в отличие от `GROUP BY`
B`PARTITION BY` сортирует строки и поэтому результат не схлопывается до одной строки на пользователя
CКонструкция `SUM(amount) OVER (...)` не поддерживается в большинстве СУБД и работает только с явным `GROUP BY`
DОконная функция считает только последний платёж пользователя, а остальные строки сохраняет как есть
Ответ: Оконные функции не меняют гранулярность результата, в отличие от `GROUP BY`.
`SUM(amount) OVER (PARTITION BY user_id)` вычисляет итог по пользователю, но возвращает этот итог в каждой исходной строке. Это удобно для долей и сравнения строки с итогом по группе. `GROUP BY user_id` схлопывает строки и действительно даёт одну строку на пользователя. Поэтому оконная агрегация не является заменой `GROUP BY`, если цель — уменьшить количество строк в результате.