Задачи на рейтинг SQL на собесе

Зачем эти задачи

Ранжирование — базовая тема на собесе middle-аналитика. «Топ-3 в каждой категории», «вторая максимальная зарплата», «5-й заказ пользователя», «топ-10% клиентов по LTV» — всё это решается через ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK. Кто не знает разницы между ними — фейлит на первом же вопросе.

На работе такие запросы встречаются ежедневно: дашборды топ-продавцов, RFM-сегментация, выбор «свежайшего» заказа каждого пользователя, определение репитеров. Понимание семантики каждой ранжирующей функции — разница между junior и middle аналитиком.

Ниже — 10 задач с разборами. Покрывают от базовых (топ-N в таблице) до продвинутых (выдача с медалями, топ-10%, кастомный порядок сортировки).

Какую функцию выбрать

Прежде чем решать задачи, разберитесь в семантике — именно её и проверяет интервьюер. Все три функции нумеруют строки внутри окна OVER (...), но по-разному ведут себя на связках (одинаковые значения):

NTILE(n) делит строки на n примерно равных корзин — им считают квартили и децили. PERCENT_RANK() возвращает относительную позицию строки от 0 до 1 — им удобно резать «топ-10%». Ключевой момент, который любят спрашивать: если нужно вернуть ровно N строк — берите ROW_NUMBER; если нужно «всех, кто попал в топ-N по значению, включая делящих место» — DENSE_RANK.

Задача 1. Топ-3 самых дорогих заказа

Простейший случай без оконных функций: сортировка и LIMIT. Оконка тут избыточна — интервьюер проверяет, что вы не усложняете там, где не надо.

SELECT * FROM orders ORDER BY total DESC LIMIT 3;

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

Классика на «топ-N в группе». Партиционируем по категории, ранжируем внутри неё, фильтруем по рангу в подзапросе. DENSE_RANK выбран сознательно: если два заказа делят третье место, вернём оба.

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

Задача 3. Вторая максимальная зарплата

Любимый вопрос. DENSE_RANK здесь важен: если максимум делят несколько сотрудников, вторая зарплата — это следующее уникальное значение, а не вторая строка. С ROW_NUMBER вы бы вернули «второго по счёту», что почти всегда неверный ответ.

SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 2
LIMIT 1;

Задача 4. Ранжировать пользователей по сумме заказов

Тут оконная функция работает поверх агрегата: сначала SUM(total) по группе, потом ранжирование по этой сумме. Показывает, что вы понимаете порядок вычислений — оконка считается после GROUP BY.

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

Задача 5. Процентили клиентов

NTILE(10) режет клиентов на 10 децилей по сумме трат — готовая основа для RFM-сегментации. Первый дециль (decile = 1) — топовые 10% по обороту.

SELECT
    user_id,
    total_spent,
    NTILE(10) OVER (ORDER BY total_spent DESC) AS decile
FROM (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders GROUP BY user_id
) t;

Задача 6. N-й заказ каждого пользователя

Здесь нужен именно ROW_NUMBER — нам важен порядковый номер заказа, а не ранг по значению. Партиционируем по пользователю, сортируем по дате, берём третью строку. Так же ищут «первую покупку» или «заказ, с которого начался отток».

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders
) t
WHERE order_num = 3;

Задача 7. Продавцы с топ-3 товарами

Ранжируем товары по продажам, отбираем тех, чей товар попал в тройку, и через DISTINCT схлопываем до списка продавцов. Частая ловушка — забыть DISTINCT и получить дубли, если у продавца несколько топовых позиций.

SELECT DISTINCT seller_id
FROM (
    SELECT *, DENSE_RANK() OVER (ORDER BY sales DESC) AS rnk
    FROM products
) t
WHERE rnk <= 3;

Задача 8. Первые и последние заказы каждого

Два ROW_NUMBER с противоположной сортировкой в одном CASE: один нумерует от старых к новым, второй — наоборот. Строка с номером 1 в прямом порядке — первый заказ, в обратном — последний. Всё, что между, помечаем как middle.

SELECT
    user_id,
    created_at,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) = 1 THEN 'first'
        WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1 THEN 'last'
        ELSE 'middle'
    END AS position
FROM orders;

Задача 9. Топ-10% платящих клиентов

PERCENT_RANK даёт относительную позицию от 0 (самый крупный клиент) до 1. Условие pr <= 0.10 отсекает верхние 10% по обороту. Обратите внимание на фильтр status = 'paid' — считаем только оплаченные заказы, иначе оборот раздувается брошенными корзинами.

SELECT * FROM (
    SELECT user_id, SUM(total) AS spend,
        PERCENT_RANK() OVER (ORDER BY SUM(total) DESC) AS pr
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
) t
WHERE pr <= 0.10;

Задача 10. Ранг с кастомной сортировкой

Ранжируем сначала по бизнес-приоритету статуса, потом по дате. Трюк — CASE внутри ORDER BY окна: он задаёт произвольный порядок статусов, которого нет в алфавите. Так делают приоритезацию тикетов, заявок в поддержку, задач в очереди.

SELECT *,
    RANK() OVER (
        ORDER BY
            CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 ELSE 3 END,
            created_at
    ) AS rnk
FROM orders;

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

• ROW_NUMBER для топ-N со связками. Если два элемента делят место, ROW_NUMBER присвоит им разные номера и один вылетит из выборки. Для «топ-N с учётом связок» нужен DENSE_RANK.
• Фильтр по рангу прямо в WHERE. Оконные функции нельзя использовать в WHERE того же запроса — они вычисляются позже. Ранжирование заворачивают в подзапрос или CTE, а фильтруют снаружи.
• Забыть ORDER BY внутри OVER. Без сортировки в окне ранг не определён — СУБД вернёт произвольный порядок, и результат будет невоспроизводимым.
• Путать RANK и DENSE_RANK в «N-й максимальной». RANK пропускает номера после связок, поэтому «второго по величине» после двух одинаковых максимумов он даст рангом 3, а не 2. Для уникальных значений почти всегда нужен DENSE_RANK.

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

• ROW_NUMBER vs RANK vs DENSE_RANK — шпаргалка
• Как посчитать ранг в SQL
• Оконные функции SQL

FAQ

ROW_NUMBER или DENSE_RANK для топ-N?

Зависит от того, что значит «топ-N». Если нужно ровно N строк (например, показать ровно 10 карточек на дашборде) — ROW_NUMBER. Если нужны все элементы, попавшие в топ-N по значению, включая тех, кто делит место — DENSE_RANK. На собесе стоит проговорить оба варианта: это показывает, что вы понимаете разницу, а не заучили один шаблон.

Как найти N-ю максимальную зарплату без оконных функций?

Через коррелированный подзапрос или LIMIT ... OFFSET. Например, SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1 вернёт вторую по величине уникальную зарплату. Оконный вариант с DENSE_RANK читается чище и легче обобщается на «N-ю в каждой группе», поэтому на собесе предпочтительнее, но знать оба способа полезно.

Почему нельзя фильтровать по оконной функции в WHERE?

Из-за порядка выполнения запроса: WHERE отрабатывает до того, как считаются оконные функции (они вычисляются на этапе SELECT, после GROUP BY). Поэтому ранг просто ещё не существует в момент фильтрации. Решение — обернуть запрос в подзапрос или CTE и фильтровать по рангу снаружи.

Чем NTILE отличается от PERCENT_RANK для сегментации?

NTILE(n) кладёт строки в n корзин примерно равного размера — удобно, когда нужны фиксированные группы (децили, квартили). PERCENT_RANK даёт непрерывную относительную позицию от 0 до 1 — удобно, когда порог задан в процентах («верхние 5%»). Для RFM-сегментов чаще берут NTILE, для отсечки «топ-X%» — PERCENT_RANK.

Как ускорить ранжирование на больших таблицах?

Помогает индекс по колонкам из ORDER BY окна и, если возможно, предварительная фильтрация до ранжирования (например, только за нужный период). Когда нужен глобальный топ-N без разбивки по группам, обычная сортировка с LIMIT часто быстрее полной оконной нумерации, потому что СУБД не считает ранг для всех строк.

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