Открытие SQL — доля категорий, накопительный итог и общий счётчик одним запросом

Условие

По таблице otp_log(client_id, sessionId, datetime, status, category) одним запросом сформировать:

1. Количество успешно введённых OTP в разрезе категорий.
2. Долю каждой категории по убыванию.
3. Количество с накопительным итогом.
4. Общее количество введённых OTP (как ещё одна колонка).
5. Последний полный месяц от текущего.

Решение

Подход

Все метрики на одном уровне агрегации (category). Окно по всем строкам сразу даёт total и накопительный итог.

Реализация

WITH last_full_month AS (
  -- Полный месяц, предшествующий текущему
  SELECT
    DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AS month_start
),
agg AS (
  SELECT
    category,
    COUNT(*) AS success_cnt
  FROM otp_log, last_full_month lfm
  WHERE status = 'success'
    AND datetime >= lfm.month_start
    AND datetime <  lfm.month_start + INTERVAL '1 month'
  GROUP BY category
)
SELECT
  category,
  success_cnt,
  ROUND(100.0 * success_cnt / SUM(success_cnt) OVER (), 2)             AS share_pct,
  SUM(success_cnt) OVER (ORDER BY success_cnt DESC
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                                                                       AS cum_cnt,
  SUM(success_cnt) OVER ()                                             AS total_cnt,
  (SELECT month_start FROM last_full_month)                            AS report_month
FROM agg
ORDER BY success_cnt DESC;

Что важно

• SUM(...) OVER () без PARTITION BY — считает по всему результату, идеально для итога и доли.
• Накопительный итог требует ORDER BY и ROWS BETWEEN ... для корректности.
• «Последний полный месяц» — на 5 мая это апрель. Формула DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') даёт начало апреля.

Ожидаемый вид

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

1. status enum. Часто 'success' пишут как 1 (boolean) или 'OK'. Проверить заранее.
2. Точность доли. Без 100.0 * целочисленное деление в некоторых СУБД даст 0.
3. Накопительный итог при равных success_cnt. Если две категории с одинаковым числом, ROWS UNBOUNDED PRECEDING корректно прибавляет одну, потом другую; RANGE слил бы их в одну точку.
4. «Последний полный месяц от текущего». Если сегодня 1-е число, то «последний полный» — это позапрошлый. Уточнить ТЗ.
5. NULL в category. Попадает ли он в результат? Обычно нужно либо отфильтровать, либо вынести в 'unknown'.

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

CTE с агрегацией COUNT(*) FILTER (WHERE status='success') по категории за последний полный месяц → итог через оконку SUM(...) OVER () для доли и total + накопительный SUM(...) OVER (ORDER BY ... DESC ROWS UNBOUNDED PRECEDING). «Последний полный месяц» — DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month').