Semrush: SQL — топ-5% платежей в каждом месяце с месячной выручкой

Условие

Дана таблица payments(payment_id, payment_date, amount) с миллионами платежей за несколько лет. Вывести для каждого месяца с начала 2021 года 5% самых крупных платежей и их сумму. Дополнительная колонка month_sum — общая выручка за этот месяц. Отсортировать по month, amount обе колонки DESC.

Решение

Подход

Используем NTILE(20) или PERCENT_RANK()/CUME_DIST() в окне по месяцу. Месячная сумма — отдельной оконной функцией без ORDER BY.

Реализация

WITH ranked AS (
  SELECT
    DATE_TRUNC('month', payment_date) AS month,
    payment_id,
    payment_date,
    amount,
    NTILE(20) OVER (
      PARTITION BY DATE_TRUNC('month', payment_date)
      ORDER BY amount DESC
    ) AS bucket,                                              -- 1 = top 5%
    SUM(amount) OVER (PARTITION BY DATE_TRUNC('month', payment_date))
        AS month_sum
  FROM payments
  WHERE payment_date >= DATE '2021-01-01'
)
SELECT month, payment_id, payment_date, amount, month_sum
FROM ranked
WHERE bucket = 1
ORDER BY month DESC, amount DESC;

Альтернатива через PERCENT_RANK

SELECT *
FROM (
  SELECT
    DATE_TRUNC('month', payment_date) AS month,
    payment_id, payment_date, amount,
    PERCENT_RANK() OVER (
      PARTITION BY DATE_TRUNC('month', payment_date)
      ORDER BY amount DESC
    ) AS pr,
    SUM(amount) OVER (PARTITION BY DATE_TRUNC('month', payment_date)) AS month_sum
  FROM payments
  WHERE payment_date >= DATE '2021-01-01'
) t
WHERE pr <= 0.05
ORDER BY month DESC, amount DESC;

Подводные камни

1. NTILE распределяет «приблизительно равно» — на стыке бакетов могут быть платежи с одинаковым amount, попадающие в разные группы. PERCENT_RANK точнее.
2. Сравнение с PERCENTILE_CONT(0.95): один граничный квантиль, потом WHERE amount >= q95 — возможный третий вариант.
3. На миллионах строк — индекс по (payment_date, amount) сильно ускорит.
4. Если в месяце < 20 платежей, топ-5% — пустое множество (или 1 платеж при NTILE(20)).

Эталонный ответ

NTILE(20) или PERCENT_RANK() <= 0.05 в окне по месяцу + SUM() OVER PARTITION BY month для month_sum.