Найди второго по сумме трат пользователя

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

Задача

Есть таблица платежей payments(user_id, amount, created_at). Найди пользователя, который занимает второе место по сумме трат.

Схема данных

CREATE TABLE payments (
  user_id int,
  amount numeric,
  created_at timestamp
);

У одного пользователя много платежей — значит, сначала нужно свести их в общую сумму, а уже потом искать «второго».

Решение

WITH spend AS (
  SELECT user_id, SUM(amount) AS total
  FROM payments
  GROUP BY user_id
),
ranked AS (
  SELECT user_id, total,
         DENSE_RANK() OVER (ORDER BY total DESC) AS rnk
  FROM spend
)
SELECT user_id, total
FROM ranked
WHERE rnk = 2
ORDER BY user_id;

Разбор

Порядок действий тут — половина ответа. Сначала GROUP BY user_id сворачивает все платежи в одну сумму на пользователя. Только после этого имеет смысл ранжировать: «второй по тратам» — это про итоговые суммы, а не про отдельные платежи. Если пропустить агрегацию, посчитаешь второй по величине платёж — совсем другой вопрос.

Дальше два честных пути. Простой — ORDER BY total DESC OFFSET 1 LIMIT 1: пропустить лидера и взять следующего. Он работает, но у него слабое место — одинаковые суммы: если два человека потратили поровну и делят первое место, OFFSET/LIMIT молча вернёт кого-то одного, и «второй» окажется на самом деле тоже «первым».

Поэтому надёжнее оконная функция DENSE_RANK(): она раздаёт места по убыванию суммы и на равных значениях даёт одинаковый ранг без пропусков. Тогда WHERE rnk = 2 вернёт всех, кто реально второй, даже если их несколько. На данных песочницы суммы у всех разные, так что второй ровно один. Про то, чем RANK отличается от DENSE_RANK и ROW_NUMBER, — в вопросе ROW_NUMBER, RANK и DENSE_RANK: разница. А каноничный «учебный» вариант этой задачи про вторую зарплату разбираем в вопросе вторая по величине зарплата.

Ожидаемый результат

user_id | total
--- | ---
102 | 520