Открытие SQL — категория-лидер OTP, индикатор и MoM по login-кодам

Условие

Дана таблица ввода OTP-кодов клиентами:

otp_log(client_id, sessionId, datetime, status, category)

Задачи:

1. Показать категорию, по которой было введено наибольшее число кодов.
2. Добавить индикатор:
   • 1 — если категория = payment;
   • 2 — если категория = transfer, но не для переводов с использованием счетов (acc);
   • остальные строки в результат не попадают.
3. Посчитать Month-of-Month (прирост текущего месяца к предыдущему) по уникальным клиентам с category = otp_login.

Решение

Задача 1 — категория-лидер

SELECT category, COUNT(*) AS cnt
FROM otp_log
GROUP BY category
ORDER BY cnt DESC
LIMIT 1;

Если нужны все категории с одинаковым максимумом, лучше через оконку:

SELECT category, cnt
FROM (
  SELECT category, COUNT(*) AS cnt,
         RANK() OVER (ORDER BY COUNT(*) DESC) AS rk
  FROM otp_log
  GROUP BY category
) t
WHERE rk = 1;

Задача 2 — индикатор

SELECT
  client_id, sessionId, datetime, status, category,
  CASE
    WHEN category = 'payment'                          THEN 1
    WHEN category = 'transfer' AND category <> 'acc'   THEN 2
    -- осторожно с формулировкой: 'переводы, но не для acc'
  END AS indicator
FROM otp_log
WHERE category = 'payment'
   OR (category = 'transfer' AND category NOT LIKE '%acc%');

В реальности фраза «не для переводов с использованием счетов» означает, что подкатегория acc хранится в category отдельным значением (например transfer_acc / transfer_card) или в отдельном поле. Уточнить у бизнеса.

Безопаснее всего:

WHERE category IN ('payment')
   OR (category LIKE 'transfer%' AND category <> 'transfer_acc')

Задача 3 — MoM по login-кодам

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', datetime)   AS m,
    COUNT(DISTINCT client_id)       AS uniq_clients
  FROM otp_log
  WHERE category = 'otp_login'
  GROUP BY DATE_TRUNC('month', datetime)
),
with_prev AS (
  SELECT
    m, uniq_clients,
    LAG(uniq_clients) OVER (ORDER BY m) AS prev_clients
  FROM monthly
)
SELECT
  m,
  uniq_clients,
  prev_clients,
  CASE
    WHEN prev_clients IS NULL OR prev_clients = 0 THEN NULL
    ELSE ROUND(100.0 * (uniq_clients - prev_clients) / prev_clients, 2)
  END AS mom_pct
FROM with_prev
ORDER BY m;

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

1. Интерпретация «не для acc». Это значение в поле category, или префикс, или отдельный флаг? В реальной задаче — обязательно уточнить.
2. COUNT(*) vs COUNT(DISTINCT client_id). В первой задаче «число введённых кодов» — это все строки. В третьей — уникальные клиенты. Перепутать легко.
3. Деление на 0 в MoM. Если в первом месяце 0 — формула выдаст ошибку; заведите явный CASE.
4. «Текущий месяц» неполный. MoM с неполным месяцем некорректен — либо считать на закрытых месяцах, либо нормировать по дням.
5. Категории с регистром / пробелами. 'payment ' ≠ 'payment'. В прод-запросе оборачивайте в LOWER(TRIM()).

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

(1) GROUP BY category ORDER BY COUNT(*) DESC LIMIT 1. (2) CASE WHEN ... WHEN ... END + WHERE-фильтр, чтобы исключить «остальные». (3) Помесячная агрегация COUNT(DISTINCT client_id) → LAG → процент роста с защитой от деления на 0.