Оконные функции SQL на собеседовании

Оконные функции — надёжный разделитель между Junior и Middle. Кандидат, уверенно работающий с OVER (PARTITION BY ... ORDER BY ...), понимает SQL значительно глубже, чем тот, кто знает только GROUP BY.

Что такое оконная функция и зачем её проверять

Оконная функция выполняет вычисления по набору строк, связанных с текущей строкой, не группируя их в одну. Это позволяет одновременно видеть и детальные данные, и агрегированные показатели.

Без оконных функций многие аналитические задачи решаются громоздкими подзапросами. С ними — одним элегантным запросом. HR и технические менеджеры должны проверять этот навык для любой Middle-позиции аналитика или разработчика.

Задача 1: ROW_NUMBER — классика интервью

-- Схема: sales(id, manager_id, amount, sale_date)
-- Задача: для каждого менеджера найдите его самую крупную сделку

SELECT manager_id, id AS sale_id, amount
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY manager_id
               ORDER BY amount DESC
           ) AS rn
    FROM sales
) t
WHERE rn = 1;

Что проверяем: базовый синтаксис оконной функции, PARTITION BY, фильтрация по результату.

Частая ошибка: кандидат пытается написать WHERE ROW_NUMBER() OVER (...) = 1 прямо в основном запросе — это синтаксическая ошибка. Нужен подзапрос или CTE.

Задача 2: RANK vs DENSE_RANK — понимание нюансов

-- Задача: пронумеруйте сотрудников по зарплате в каждом отделе.
-- Покажите разницу между RANK и DENSE_RANK при одинаковых зарплатах.

SELECT
    name,
    department_id,
    salary,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_pos,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_pos
FROM employees;

Пример вывода при двух сотрудниках с одинаковой зарплатой:

Что проверяем: понимание разницы. Спросите: «В каких бизнес-задачах важно использовать DENSE_RANK вместо RANK?»

Задача 3: LAG и LEAD — анализ динамики

-- Схема: monthly_revenue(month DATE, revenue NUMERIC)
-- Задача: рассчитайте абсолютный прирост и процентное изменение
-- выручки по сравнению с предыдущим месяцем

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)                              AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month)                   AS abs_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month),
        1
    )                                                               AS pct_change
FROM monthly_revenue
ORDER BY month;

Что проверяем: LAG/LEAD, арифметика в оконных функциях.

Уточняющий вопрос: «Что произойдёт с первой строкой, у которой нет предыдущего месяца?» Правильный ответ: LAG вернёт NULL, все вычисления с NULL дадут NULL.

Задача 4: Скользящая сумма и среднее

-- Задача: рассчитайте скользящую сумму продаж за последние 3 дня включительно

SELECT
    sale_date,
    daily_amount,
    SUM(daily_amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3d_sum,
    AVG(daily_amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3d_avg
FROM daily_sales;

Что проверяем: понимание ROWS/RANGE BETWEEN, разница между накопительным итогом и скользящим окном.

Сложный вопрос: «Чем ROWS BETWEEN отличается от RANGE BETWEEN?» Правильный ответ: ROWS работает с физическими строками (порядковый номер), RANGE — со значениями (все строки с одинаковым значением ORDER BY попадают в одно окно).

Задача 5: NTILE — разбивка на квантили

-- Задача: разбейте клиентов на 4 группы (квартили) по объёму покупок

SELECT
    customer_id,
    total_purchases,
    NTILE(4) OVER (ORDER BY total_purchases DESC) AS quartile
FROM customer_stats;

Что проверяем: знание менее популярных, но полезных оконных функций.

Задача 6: Накопительный итог (Running Total)

-- Задача: для каждой транзакции покажите накопленный итог баланса счёта

SELECT
    transaction_date,
    amount,
    type,
    SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END)
        OVER (ORDER BY transaction_date, id) AS running_balance
FROM transactions
WHERE account_id = 42;

Что проверяем: накопительная оконная функция, CASE WHEN внутри SUM.

Как оценивать знание оконных функций

Типичные вопросы для собеседования

• «Можно ли использовать оконную функцию в WHERE?»
• «На каком этапе выполнения запроса применяются оконные функции?»
• «Как найти второй по величине элемент в каждой группе?»

Правильные ответы: нет (только в подзапросе), после WHERE и GROUP BY, через ROW_NUMBER или DENSE_RANK = 2.

Предложите кандидатам попрактиковаться с оконными функциями на SQLlab.ru — там есть целый раздел с задачами именно по этой теме.