Вопросы по теме «Оконные функции»
Оконные функции — один из самых частых вопросов на собеседовании аналитика. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER — всё это нужно знать наизусть. Интервьюеры проверяют, умеете ли вы решать задачи без подзапросов и самосоединений.
Всего в этом разделе 20 вопросов. Каждый — с правильным ответом и кратким разбором теории. Разбито на 4 части по 5 вопросов.
Вопросы 1–5 из 20
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` задаст порядок накопления по времени.
2Нужно пронумеровать заказы каждого пользователя по времени покупки, начиная с 1, чтобы потом найти 1-й, 2-й, 3-й заказ. Какое выражение подходит?
A`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_at)`
B`ROW_NUMBER() OVER (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`, упорядочивая заказы по `order_at`. Без `PARTITION BY` номера будут общими на весь датасет. `RANK()` и `DENSE_RANK()` полезны для мест/тайов, но для «какая по счету покупка» обычно нужен именно `ROW_NUMBER()`.
3Вы хотите сравнить текущую метрику с метрикой следующего периода во временном ряду. Какая функция возвращает «следующее» значение относительно текущей строки по порядку `ORDER BY`?
A`LEAD`
B`LAG`
C`RANK`
D`ROW_NUMBER`
Ответ: `LEAD` смотрит вперед по окну, а `LAG` — назад.
В окне `... OVER (PARTITION BY ... ORDER BY ...)` функция `LEAD(x)` возвращает значение `x` из следующей строки относительно текущей по порядку `ORDER BY`. Функция `LAG(x)` возвращает значение из предыдущей строки. Это базовый прием для сравнений «период к периоду» (вперед или назад).
4Для каждой покупки пользователя нужно добавить дату следующей покупки этого же пользователя (чтобы потом посчитать интервал между покупками). Что использовать?
A`LEAD(order_at) OVER (PARTITION BY user_id ORDER BY order_at)`
B`LAG(order_at) OVER (PARTITION BY user_id ORDER BY order_at)`
C`LEAD(order_at) OVER (ORDER BY order_at)`
D`RANK() 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` смотрит назад (предыдущая покупка), а без `PARTITION BY user_id` вы получите «следующую покупку» другого пользователя.
5Вы хотите получить по одному ряду на пользователя (итоговая выручка), и вместо `GROUP BY` используете `SUM(amount) OVER (PARTITION BY user_id)`. Почему это не дает одну строку на пользователя?
AПотому что оконные функции нельзя использовать с `SUM`
BПотому что `PARTITION BY` сортирует строки, а `GROUP BY` — нет
CПотому что `GROUP BY` обязательно должен идти вместе с `ORDER BY`
DПотому что оконная агрегация сохраняет исходные строки и только добавляет вычисленную колонку, а `GROUP BY` агрегирует строки до одной на группу
Ответ: Оконные функции не меняют гранулярность результата, в отличие от `GROUP BY`.
`SUM(amount) OVER (PARTITION BY user_id)` вычисляет итог по пользователю, но возвращает этот итог в каждой исходной строке пользователя. Это удобно для долей и сравнения строки с итогом. `GROUP BY user_id` схлопывает строки и действительно дает одну строку на пользователя. Поэтому оконная агрегация не является заменой `GROUP BY`, если цель — уменьшить количество строк в результате.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram