Оконные функции SQL: шпаргалка для собеседования

Зачем аналитику оконные функции

Оконные функции — тема номер один на техническом собеседовании аналитика уровня middle и выше. Они позволяют делать вычисления по группам строк, не сворачивая результат в одну строку, как GROUP BY. Это незаменимо для расчёта рангов, нарастающих итогов, скользящих средних и сравнения строк между собой.

Если вы готовитесь к собеседованию по SQL, оконные функции — первое, что нужно довести до автоматизма.

Общий синтаксис

функция() OVER (
    PARTITION BY столбец_группировки
    ORDER BY столбец_сортировки
    ROWS BETWEEN начало AND конец
)

• PARTITION BY — делит данные на группы (окна). Аналог GROUP BY, но без свёртки строк.
• ORDER BY — задаёт порядок строк внутри окна.
• ROWS BETWEEN — определяет рамку: какие строки участвуют в вычислении.

Не все части обязательны — зависит от конкретной функции.

Функции ранжирования

ROW_NUMBER()

Присваивает уникальный порядковый номер каждой строке внутри окна.

SELECT
    user_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_num
FROM orders

Результат: у каждого пользователя заказы пронумерованы 1, 2, 3… по дате. Номера всегда уникальны, даже при одинаковых значениях order_date.

На собеседовании спрашивают: «Найдите первый заказ каждого пользователя». Решение — обернуть в подзапрос и отфильтровать WHERE order_num = 1.

RANK()

Присваивает ранг с учётом одинаковых значений. При дубликатах — одинаковый ранг, следующий номер пропускается.

SELECT
    student_id,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM exam_results

Если у двух студентов балл 95, оба получат ранг 1. Следующий студент получит ранг 3 (не 2).

DENSE_RANK()

Как RANK(), но без пропусков в нумерации.

SELECT
    student_id,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_results

Два студента с баллом 95 — ранг 1. Следующий — ранг 2.

Классический вопрос на собеседовании: «Чем отличаются ROW_NUMBER, RANK и DENSE_RANK?» Ответ: ROW_NUMBER всегда уникален, RANK пропускает номера при дубликатах, DENSE_RANK не пропускает.

NTILE(n)

Делит строки на n равных групп.

SELECT
    user_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM user_stats

Пользователи разбиты на 4 квартиля по сумме покупок. Первый квартиль — топ-25% по расходам.

На собеседовании: используется в задачах про сегментацию — «Разделите пользователей на 10 равных групп по активности».

Функции смещения

LAG(столбец, смещение)

Возвращает значение из предыдущей строки (или на n строк назад).

SELECT
    DATE,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY DATE) AS prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY DATE) AS daily_change
FROM daily_stats

Получаем выручку за предыдущий день и разницу — рост или падение.

LEAD(столбец, смещение)

Обратная LAG — берёт значение из следующей строки.

SELECT
    user_id,
    session_date,
    LEAD(session_date, 1) OVER (PARTITION BY user_id ORDER BY session_date) AS next_session
FROM sessions

Для каждой сессии пользователя видим дату следующей сессии. Если следующей нет — NULL.

На собеседовании: «Посчитайте среднее время между сессиями пользователя». Решение — через LAG или LEAD вычислить разницу дат, затем AVG.

Агрегатные функции с окном

SUM() OVER

Нарастающий итог — одна из самых частых задач.

SELECT
    DATE,
    new_users,
    SUM(new_users) OVER (ORDER BY DATE) AS cumulative_users
FROM daily_registrations

Каждая строка показывает сумму всех предыдущих значений включая текущее — нарастающий итог регистраций.

Нарастающий итог по группам:

SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total
FROM orders

AVG() OVER

Скользящее среднее — незаменимо для сглаживания метрик.

SELECT
    DATE,
    dau,
    AVG(dau) OVER (
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS dau_7d_avg
FROM daily_metrics

Среднее DAU за последние 7 дней (текущий + 6 предыдущих). Именно так считают скользящее среднее в продуктовой аналитике.

Оконные рамки: ROWS BETWEEN

Рамка определяет, какие строки участвуют в вычислении относительно текущей.

Синтаксис	Значение
UNBOUNDED PRECEDING	С начала окна
n PRECEDING	n строк назад
CURRENT ROW	Текущая строка
n FOLLOWING	n строк вперёд
UNBOUNDED FOLLOWING	До конца окна

Примеры:

-- Сумма текущей + 2 предыдущих строки
SUM(x) OVER (ORDER BY DATE ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Среднее в окне ±1 строка от текущей
AVG(x) OVER (ORDER BY DATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Нарастающий итог с начала
SUM(x) OVER (ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Важный нюанс: по умолчанию (без явного ROWS BETWEEN) при наличии ORDER BY рамка = RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это ведёт себя иначе при дубликатах в ORDER BY. На собеседовании этот вопрос задают, чтобы проверить глубину понимания.

Комбинированные задачи

На собеседованиях часто просят совместить несколько оконных функций. Пример:

Задача: Для каждого пользователя найдите заказ с максимальной суммой и выведите долю этого заказа от общей суммы покупок.

WITH ranked AS (
    SELECT
        user_id,
        order_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
        SUM(amount) OVER (PARTITION BY user_id) AS total_amount
    FROM orders
)
SELECT
    user_id,
    order_id,
    amount,
    ROUND(amount::numeric / total_amount * 100, 1) AS pct_of_total
FROM ranked
WHERE rn = 1

Здесь ROW_NUMBER находит максимальный заказ, а SUM OVER считает общую сумму по пользователю — обе функции в одном запросе.

15 задач на оконные функции

Задачи от простых к сложным — как на настоящем собеседовании.

Задача 1. Ранжирование клиентов по выручке

Пронумеруйте клиентов по убыванию суммарных покупок.

SELECT
  user_id,
  SUM(amount) AS total,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM orders
GROUP BY user_id;

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

WITH ranked AS (
  SELECT product_id, category,
    SUM(quantity) AS sold,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(quantity) DESC) AS rn
  FROM order_items JOIN products USING (product_id)
  GROUP BY product_id, category
)
SELECT * FROM ranked WHERE rn = 1;

Задача 3. Разница между RANK и DENSE_RANK

Если два товара имеют одинаковые продажи: ROW_NUMBER даст разные номера (1, 2), RANK — одинаковые с пропуском (1, 1, 3), DENSE_RANK — одинаковые без пропуска (1, 1, 2). Выбирайте в зависимости от задачи.

Задача 4. Выручка за предыдущий месяц (LAG)

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly;

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

SELECT created_at::DATE AS day, SUM(amount) AS daily,
  SUM(SUM(amount)) OVER (ORDER BY created_at::DATE
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative
FROM orders
GROUP BY created_at::DATE;

Задача 6. Скользящее среднее за 7 дней

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

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — это 7 строк (текущая + 6 предыдущих).

Задача 7. Доля каждого заказа от общей выручки пользователя

SELECT order_id, user_id, amount,
  ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY user_id), 1) AS pct
FROM orders;

SUM OVER без ORDER BY считает сумму по всему окну (все заказы пользователя).

Задача 8. Первый и последний заказ пользователя

SELECT DISTINCT user_id,
  FIRST_VALUE(order_id) OVER w AS first_order,
  LAST_VALUE(order_id) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

Задача 9. Время между последовательными заказами

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

Задача 10. Нарастающий итог с обнулением каждый месяц

SELECT created_at::DATE AS day, amount,
  SUM(amount) OVER (
    PARTITION BY DATE_TRUNC('month', created_at)
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS monthly_cumulative
FROM orders;

PARTITION BY месяц обнуляет сумму в начале каждого месяца.

Задача 11. Процентиль пользователя по активности

SELECT user_id, total_orders,
  PERCENT_RANK() OVER (ORDER BY total_orders) AS percentile
FROM (
  SELECT user_id, COUNT(*) AS total_orders FROM orders GROUP BY user_id
) t;

PERCENT_RANK возвращает значение от 0 до 1 — позиция пользователя среди всех.

Задача 12. Разница между текущим и максимальным значением в группе

SELECT user_id, order_id, amount,
  MAX(amount) OVER (PARTITION BY user_id) - amount AS diff_from_max
FROM orders;

Задача 13. Нумерация с перезапуском при смене группы

SELECT user_id, category, order_id,
  ROW_NUMBER() OVER (PARTITION BY user_id, category ORDER BY created_at) AS order_in_category
FROM orders JOIN products USING (product_id);

Задача 14. NTILE — разбиение на квартили

SELECT user_id, total_spent,
  NTILE(4) OVER (ORDER BY total_spent) AS quartile
FROM (
  SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id
) t;

NTILE(4) делит все строки на 4 равные группы. Полезно для RFM-сегментации.

Задача 15. Сложная: воронка с проверкой последовательности событий

WITH ordered AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'view') AS step1,
  COUNT(DISTINCT user_id) FILTER (
    WHERE event_name = 'view' AND next_event = 'cart'
  ) AS step2,
  COUNT(DISTINCT user_id) FILTER (
    WHERE event_name = 'cart' AND next_event = 'purchase'
  ) AS step3
FROM ordered;

LEAD помогает проверить, что события идут в правильном порядке.

---

Как тренироваться

Оконные функции нужно не просто понять — их нужно написать руками десятки раз. Теория забывается, навык остаётся.

Тренажёр Карьерник содержит вопросы по оконным функциям с разборами: от базового отличия RANK и ROW_NUMBER до сложных комбинаций с ROWS BETWEEN. Можно тренироваться по 15 минут в день в Telegram — этого достаточно, чтобы за неделю довести тему до автоматизма.

Больше примеров вопросов по SQL — в разделе подготовки. А если готовитесь комплексно, посмотрите примеры вопросов по всем темам.

Читайте также

• Pandas шпаргалка для аналитика
• Как считать Retention
• Подготовка к собеседованию аналитика

FAQ

Какие оконные функции чаще всего спрашивают на собеседовании?

ROW_NUMBER, RANK и LAG/LEAD — абсолютные лидеры. За ними идут SUM OVER для нарастающих итогов и AVG OVER для скользящих средних. NTILE встречается реже, но появляется в задачах на сегментацию. Обязательно знайте разницу между ROW_NUMBER, RANK и DENSE_RANK — этот вопрос задают почти всегда.

Чем ROWS отличается от RANGE в оконной рамке?

ROWS считает физические строки: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — это ровно 3 строки. RANGE работает по значениям: если в ORDER BY есть дубликаты, RANGE включит все строки с одинаковым значением. На практике почти всегда нужен ROWS. Если не указать рамку явно, по умолчанию используется RANGE — это частая ловушка на собеседованиях.

Можно ли использовать несколько оконных функций в одном запросе?

Да, и это делают постоянно. Каждая оконная функция может иметь свой PARTITION BY и ORDER BY. Например, в одном SELECT можно одновременно пронумеровать строки через ROW_NUMBER и посчитать нарастающую сумму через SUM OVER — они будут работать независимо.

---

Тренируйся к собесу в @kariernik_bot — 1500+ вопросов по SQL, Python, A/B и метрикам в Telegram.