15 апреля 2026 г.

Задачи на оконные функции на собеседовании аналитика

Что спрашивают про оконные функции

Оконные функции — главный блок SQL на middle-собесах. Задачи обычно требуют:

  • Ранжирования (топ-N в группе).
  • Сравнения соседних строк (LAG/LEAD).
  • Нарастающих итогов и скользящих средних.
  • Комбинации нескольких оконных функций.

Ниже 15 задач от базовых к сложным. Каждая — с реальных собеседований в Яндекс, Авито, Ozon, Wildberries.

Базовые

1. Номер заказа у каждого пользователя

SELECT user_id, order_id, created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
FROM orders;

2. Ранг по баллам с обработкой дубликатов

SELECT student_id, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM exam_results;

Интервьюер спросит отличия. Ответ:

  • ROW_NUMBER: уникальные номера 1, 2, 3, 4.
  • RANK: при дубле 95→95 будет 1, 1, 3, 4 (пропуск).
  • DENSE_RANK: 1, 1, 2, 3 (без пропуска).

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

WITH ranked AS (
    SELECT category, product_id, sold_count,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sold_count DESC) AS rn
    FROM product_sales
)
SELECT category, product_id, sold_count FROM ranked WHERE rn = 1;

Если хочется сразу закрепить тему на практике — открой тренажёр в Telegram. 10 минут в день — и синтаксис в пальцах.

LAG / LEAD

4. Выручка за предыдущий день

SELECT day, revenue,
    LAG(revenue) OVER (ORDER BY day) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY day) AS diff
FROM daily_revenue;

5. Рост MoM в процентах

WITH monthly AS (
    SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS rev
    FROM orders GROUP BY 1
)
SELECT month, rev,
    ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY month)) /
        LAG(rev) OVER (ORDER BY month), 2) AS mom_pct
FROM monthly;

6. Время между сессиями пользователя

SELECT user_id, session_time,
    session_time - LAG(session_time) OVER (PARTITION BY user_id ORDER BY session_time) AS gap
FROM sessions;

Дальше можно посчитать AVG gap — типичное задание.

7. Последовательные события в воронке

-- Проверить, что view → add_to_cart произошло у пользователя
WITH seq AS (
    SELECT user_id, event_name,
        LAG(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event
    FROM events
)
SELECT DISTINCT user_id
FROM seq
WHERE event_name = 'add_to_cart' AND prev_event = 'view';

Агрегаты с OVER

8. Нарастающий итог регистраций по дням

SELECT day, new_users,
    SUM(new_users) OVER (ORDER BY day) AS total_users
FROM daily_signups;

9. Скользящее среднее DAU за 7 дней

SELECT day, dau,
    AVG(dau) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS dau_ma_7d
FROM daily_active_users;

Ключевое: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW = 7 строк (6 + текущая).

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

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

SUM OVER без ORDER BY — общая сумма группы (не нарастающая).

11. Разница между текущей и максимальной в группе

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

Сложные

12. Второй по выручке пользователь

SELECT user_id, total
FROM (
    SELECT user_id, SUM(amount) AS total,
        DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
    FROM orders GROUP BY user_id
) t
WHERE rnk = 2;

Через DENSE_RANK — не пропустим при равенстве.

13. Топ-3 в каждой категории (classic)

WITH ranked AS (
    SELECT category, product_id, revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM product_stats
)
SELECT * FROM ranked WHERE rn <= 3;

14. Заказ, следующий после отказа

SELECT user_id, order_id, amount
FROM (
    SELECT user_id, order_id, amount, status,
        LAG(status) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_status
    FROM orders
) t
WHERE prev_status = 'refunded';

15. Session-based анализ: сколько событий в каждой сессии

-- Сессия = 30+ минут простоя
WITH diffs AS (
    SELECT user_id, event_time,
        event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS gap
    FROM events
),
sessions AS (
    SELECT user_id, event_time,
        SUM(CASE WHEN gap > INTERVAL '30 minute' OR gap IS NULL THEN 1 ELSE 0 END)
            OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
    FROM diffs
)
SELECT user_id, session_id, COUNT(*) AS events_count
FROM sessions
GROUP BY user_id, session_id;

Известный приём: SUM OVER с 1/0 по условию даёт «идентификатор» сессии. Встречается на senior-собесах.

Чтобы не только читать теорию, но и решать реальные задачи — загляните в бот Карьерника. Там по каждой теме подборка вопросов с разборами.

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

Оконные функции — это паттерны. Чем больше задач решили, тем быстрее распознаёте «эта задача = ROW_NUMBER», «эта = LAG», «эта = SUM OVER».

Тренажёр Карьерник содержит блок оконных функций: 30+ задач с разборами. От топ-N в группе до session identification. Идеально за неделю-две до собеса.

Совет: на собесе, услышав «топ-N», «сравнить с предыдущим», «нарастающий итог», «скользящее среднее» — сразу думайте про оконные. Это почти всегда они.

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

FAQ

Какая оконная функция самая частая на собесе?

ROW_NUMBER — точно. Используется для топ-N, дедупликации, нумерации. Следом LAG для MoM/YoY и сравнений. SUM OVER для нарастающих итогов.

Обязательно ли знать PERCENT_RANK и NTILE?

На junior — нет. На middle — желательно NTILE для задач про квартили. PERCENT_RANK встречается редко.

Чем ROWS отличается от RANGE?

ROWS считает физические строки (ROWS BETWEEN 2 PRECEDING — ровно 3 строки). RANGE работает по значениям (если есть дубли в ORDER BY — RANGE включит их все). Почти всегда нужен ROWS. RANGE — дефолт без явного указания.

Почему оконные функции в WHERE не работают?

Оконные функции вычисляются после WHERE (в фазе SELECT). Поэтому фильтрация через WHERE window_function — не работает. Решение: обернуть в подзапрос/CTE и фильтровать на следующем уровне.

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