ПрограммированиеData Science и MLMiddle

SQL: есть таблица payments (user_id, paid_at, amount). Найдите для каждого пользователя три самых крупных платежа и посчитайте долю каждого в его общей сумме.

Короткий ответ

Оконные функции с PARTITION BY user_id
ROW_NUMBER по убыванию amount для ранга платежа
SUM(amount) OVER по пользователю — без схлопывания строк
Фильтр ранга только через подзапрос или CTE
Доля: amount делить на оконную сумму
Обсудить ROW_NUMBER против RANK при равных суммах

Задача на связку ROW_NUMBER и оконного SUM по партиции пользователя с фильтрацией ранга в CTE — классика секции SQL.

Как сказать вслух

пример ответа

Здесь нужны оконные функции. В CTE я нумерую платежи каждого пользователя по убыванию суммы через ROW_NUMBER и тем же махом считаю оконную сумму всех его платежей. Потом во внешнем запросе фильтрую ранг не больше трёх и делю платёж на общую сумму — это и есть доля. Отдельно уточнил бы, как поступать с равными платежами: для строго трёх строк беру ROW_NUMBER, для всех с одинаковой суммой — RANK.

Подробный ответ

Основной ответ

Решение строится на двух оконных функциях в одном CTE: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) даёт ранг платежа внутри пользователя, а SUM(amount) OVER (PARTITION BY user_id) — общую сумму пользователя без GROUP BY, то есть сохраняя каждую строку. Фильтровать по оконной функции в том же SELECT нельзя — она вычисляется после WHERE, поэтому нужен подзапрос или CTE. Доля — amount / total; в целочисленных диалектах нужно привести к numeric. Стоит проговорить выбор функции ранжирования: ROW_NUMBER гарантирует ровно три строки, RANK вернёт больше при равенстве сумм, DENSE_RANK не оставляет пропусков; детерминизма добиваются добавлением paid_at в ORDER BY окна.

Ключевые моменты

Два окна в одном CTE. Ранжирование и оконная сумма считаются рядом по одной партиции — без джойна с агрегатом.
Фильтр через CTE. WHERE не видит оконные функции; фильтрация ранга — только во внешнем запросе.
ROW_NUMBER против RANK. Поведение при ничьих — любимый уточняющий вопрос; выбор зависит от постановки.
Деление без сюрпризов. Привести к numeric и помнить про деление на ноль при нулевых суммах.

Практический контекст

Топ-N на группу — самая частая оконная задача в SQL-секциях аналитиков и DS: по этому же шаблону ищут последние события, первые покупки, скользящие суммы. Интервьюер смотрит, знает ли кандидат порядок выполнения запроса и поведение при ничьих. В работе такие запросы — основа витрин и когортных отчётов.

Пример кода

-- SQL (PostgreSQL)
WITH ranked AS (
    SELECT
        user_id,
        paid_at,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY amount DESC, paid_at
        ) AS rn,
        SUM(amount) OVER (PARTITION BY user_id) AS user_total
    FROM payments
)
SELECT
    user_id,
    paid_at,
    amount,
    ROUND(amount::numeric / NULLIF(user_total, 0), 4) AS share
FROM ranked
WHERE rn <= 3
ORDER BY user_id, rn;

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

Пытаются фильтровать WHERE rn <= 3 в том же SELECT, где объявлено окно
Используют GROUP BY для общей суммы и теряют построчную детализацию платежей
Не задумываются о поведении при равных суммах и недетерминированном порядке

ИП Кочкин Алексей Сергеевич · ИНН 390509026279 · ОГРНИП 325390000030973 · jiniys2005@yandex.ru