Задачи по оконным функциям SQL

Зачем это знать

Оконные функции — точка водораздела между junior и middle SQL. Без них — пишете громоздкие self-join и subqueries. С ними — элегантные запросы в одну строку. На собесах window functions встречаются в 80% SQL-задач middle+.

Задача 1: топ-3 по каждой категории

«Для каждой категории — 3 продукта с наибольшей выручкой».

SELECT *
FROM (
    SELECT
        category,
        product,
        revenue,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
    FROM products
) t
WHERE rnk <= 3;

DENSE_RANK обрабатывает ties (два продукта с одинаковой revenue считаются одним rank).

Задача 2: running total

«Накопительная выручка по дням».

SELECT
    day,
    revenue,
    SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily;

Задача 3: Month-over-month change

«Изменение выручки к предыдущему месяцу».

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS mom_diff,
    (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 /
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS mom_pct
FROM monthly;

Задача 4: процент от общего

«Вклад каждого продукта в общую выручку».

SELECT
    product,
    revenue,
    revenue * 100.0 / SUM(revenue) OVER () AS pct_of_total
FROM products;

Задача 5: 7-day moving average

«7-дневная скользящая средняя».

SELECT
    day,
    revenue,
    AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily;

Задача 6: первая покупка пользователя

SELECT
    user_id,
    order_id,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS purchase_num
FROM orders
WHERE purchase_num = 1;

Неверно! — WHERE выполняется до window function. Правильно:

SELECT * FROM (
    SELECT
        user_id,
        order_id,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS purchase_num
    FROM orders
) t
WHERE purchase_num = 1;

Либо QUALIFY в BigQuery / Snowflake.

Задача 7: разница между покупками

«Для каждого user — разница (дни) между последовательными заказами».

SELECT
    user_id,
    order_id,
    created_at,
    created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS days_since_prev
FROM orders;

Задача 8: процент от категории

«Вклад каждого продукта в выручку своей категории».

SELECT
    product,
    category,
    revenue,
    revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category) AS pct_of_cat
FROM products;

Задача 9: cumulative users

«Накопленное число новых users по месяцам».

SELECT
    month,
    new_users,
    SUM(new_users) OVER (ORDER BY month) AS cumulative_users
FROM monthly_stats;

Задача 10: первое событие каждого типа

«Для каждого user — первое событие каждого type».

SELECT * FROM (
    SELECT
        user_id,
        event_type,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY created_at) AS rn
    FROM events
) t
WHERE rn = 1;

Задача 11: median per group

SELECT
    category,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) OVER (PARTITION BY category) AS median_price
FROM products;

Но это скорее ordered-set aggregate. Для median в window — не все БД поддерживают.

Задача 12: gaps and islands

«Найти непрерывные дни активности для user».

WITH numbered AS (
    SELECT
        user_id,
        day,
        day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
    FROM logins
)
SELECT user_id, MIN(day), MAX(day), COUNT(*) AS streak
FROM numbered
GROUP BY user_id, grp;

Classic pattern — см. отдельную статью как найти цепочки подряд.

ROW_NUMBER vs RANK vs DENSE_RANK

Values	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2
80	4	4	3

ROW_NUMBER — уникальные. RANK — повторы + пропуски. DENSE_RANK — повторы без пропусков.

Частые ошибки

WHERE на window function

Не работает. Используйте subquery или QUALIFY.

Забыть PARTITION BY

ROW_NUMBER() OVER (ORDER BY col) — номер в всём наборе. С PARTITION — per группу.

Неправильные frame

ROWS BETWEEN ... default может отличаться для разных aggregates.

На собесе

Знайте 5 core patterns:

1. Топ-N per группу (DENSE_RANK)
2. Running total (SUM OVER)
3. MoM diff (LAG)
4. % of total (SUM OVER ())
5. Moving average (ROWS BETWEEN)

С этим пройдёте 90% задач.

Связанные темы

• 50 вопросов SQL
• Задачи SQL на собесе
• Задачи на оконные функции
• Шпаргалка по оконным функциям

FAQ

В MySQL window functions?

Только с 8.0. До — через переменные.

Медленнее без window?

Обычно window быстрее self-join.

Все БД поддерживают?

Postgres, MSSQL, Oracle, Snowflake, BigQuery, MySQL 8+.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.