9 апреля 2026 г.

SQL-задачи на оконные функции — с решениями

Зачем решать задачи на оконные функции

На собеседовании аналитика уровня middle не спрашивают «что такое OVER» — дают задачу и просят написать запрос. Без практики даже знакомый синтаксис не поможет.

Оконные функции — одна из самых частых тем на SQL-собеседованиях. Теорию знают многие, а вот написать рабочий запрос за 5 минут под давлением — единицы. Разница между «знаю» и «умею» закрывается только практикой.

Ниже — 5 задач уровня middle. Каждая задача — реальный сценарий из аналитики: ранжирование, сравнение строк, нарастающие итоги. Если хотите разобрать теорию перед практикой — загляните в полный гайд по оконным функциям.

Задача 1. Топ-3 продукта по выручке в каждой категории

Условие. Таблица products:

product_id	category	product_name	revenue
1	Электроника	Ноутбук	500000
2	Электроника	Планшет	320000
3	Электроника	Наушники	150000
4	Электроника	Кабель	20000
5	Одежда	Куртка	180000
6	Одежда	Джинсы	120000
...	...	...	...

Выведите топ-3 продукта по выручке в каждой категории.

Решение:

SELECT category, product_name, revenue
FROM (
    SELECT
        category,
        product_name,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3

Разбор. PARTITION BY category создаёт отдельное окно для каждой категории. ORDER BY revenue DESC сортирует продукты внутри окна по убыванию выручки. ROW_NUMBER() нумерует строки 1, 2, 3... — остаётся отфильтровать первые три. Если нужно сохранить продукты с одинаковой выручкой на одной позиции — используйте DENSE_RANK() вместо ROW_NUMBER(). Подробнее о разнице — в статье RANK vs ROW_NUMBER vs DENSE_RANK.

Задача 2. Разница между текущей и предыдущей покупкой пользователя

Условие. Таблица orders:

order_id	user_id	order_date	amount
101	1	2026-01-05	3000
102	1	2026-01-18	4500
103	1	2026-02-10	2000
104	2	2026-01-12	7000
105	2	2026-02-01	5500

Для каждого заказа покажите сумму предыдущего заказа того же пользователя и разницу.

Решение:

SELECT
    order_id,
    user_id,
    order_date,
    amount,
    LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_amount,
    amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS diff
FROM orders

Разбор. LAG(amount) берёт значение amount из предыдущей строки внутри окна. Для первого заказа каждого пользователя предыдущего значения нет — функция вернёт NULL. Если нужно подставить 0 вместо NULL, используйте LAG(amount, 1, 0) — третий аргумент задаёт значение по умолчанию. Окно PARTITION BY user_id ORDER BY order_date гарантирует, что сравнение идёт между заказами одного пользователя в хронологическом порядке.

Задача 3. Нарастающий итог выручки по месяцам

Условие. Таблица monthly_revenue:

month	revenue
2026-01-01	1200000
2026-02-01	1450000
2026-03-01	980000
2026-04-01	1600000

Рассчитайте нарастающий итог (running total) выручки по месяцам.

Решение:

SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_revenue

Разбор. SUM(revenue) OVER (ORDER BY month) — агрегатная функция в оконном режиме. Когда указан ORDER BY без явного frame clause, по умолчанию используется рамка ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — то есть сумма считается от первой строки до текущей. Это и есть нарастающий итог. Для скользящего среднего за 3 месяца используйте AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). Больше примеров с SUM OVER — в шпаргалке по оконным функциям.

Задача 4. Пользователи, чей заказ превысил их среднюю сумму

Условие. Таблица orders:

order_id	user_id	amount
201	1	3000
202	1	7000
203	1	4000
204	2	10000
205	2	2000
206	2	5000

Выведите заказы, сумма которых больше средней суммы заказов этого пользователя.

Решение:

SELECT order_id, user_id, amount, user_avg
FROM (
    SELECT
        order_id,
        user_id,
        amount,
        AVG(amount) OVER (PARTITION BY user_id) AS user_avg
    FROM orders
) t
WHERE amount > user_avg

Разбор. AVG(amount) OVER (PARTITION BY user_id) вычисляет среднюю сумму заказов каждого пользователя и добавляет её в каждую строку — без свёртки данных. Без оконной функции пришлось бы считать среднюю в подзапросе и джойнить обратно. Обратите внимание: нельзя фильтровать по оконной функции прямо в WHERE — нужен подзапрос или CTE.

Задача 5. Ранг отдела по ФОТ и место каждого сотрудника

Условие. Таблица employees:

emp_id	name	department	salary
1	Иванов	Продажи	80000
2	Петрова	Продажи	95000
3	Сидоров	IT	120000
4	Козлова	IT	110000
5	Морозов	Маркетинг	70000

Для каждого сотрудника выведите: суммарный ФОТ его отдела, ранг отдела по ФОТ среди всех отделов и ранг сотрудника по зарплате внутри своего отдела.

Решение:

SELECT
    name,
    department,
    salary,
    dept_total,
    RANK() OVER (ORDER BY dept_total DESC) AS dept_rank,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS emp_rank_in_dept
FROM (
    SELECT
        name,
        department,
        salary,
        SUM(salary) OVER (PARTITION BY department) AS dept_total
    FROM employees
) t

Разбор. Задача в два шага. Сначала во внутреннем подзапросе считаем суммарный ФОТ отдела через SUM(salary) OVER (PARTITION BY department) — значение дублируется для каждого сотрудника отдела. Затем во внешнем запросе строим два ранга: RANK() OVER (ORDER BY dept_total DESC) ранжирует отделы по ФОТ (одинаковый dept_total — одинаковый ранг), а RANK() OVER (PARTITION BY department ORDER BY salary DESC) ранжирует сотрудников внутри своего отдела. Два оконных выражения в одном SELECT — нормальная практика, каждое работает в своём окне.

Что запомнить

ROW_NUMBER + PARTITION BY — классический паттерн top-N per group. Встречается почти на каждом собеседовании.
LAG / LEAD — сравнение текущей строки с предыдущей или следующей. Удобно для расчёта дельт и цепочек событий.
SUM OVER (ORDER BY) — нарастающий итог. Без явной рамки суммирует от начала до текущей строки.
AVG OVER (PARTITION BY) — среднее по группе без свёртки. Позволяет сравнивать строку с агрегатом в одном запросе.
RANK — ранжирование с одинаковыми позициями при дубликатах. Для задач без дубликатов взаимозаменяем с ROW_NUMBER.

Хотите отработать оконные функции в формате тренажёра — откройте тренажёр. 1500+ вопросов по SQL и другим темам для собеседования аналитика.

Ещё по теме:

Тренироваться в Telegram