LeetCode SQL — Monthly Transactions I: pivot approved/всё по странам

Условие

Таблица Transactions(id, country, state, amount, trans_date), state ∈ {approved, declined}.

Для каждой пары (month, country) верните:

• trans_count — всего транзакций,
• approved_count — одобренных,
• trans_total_amount — суммарный объём,
• approved_total_amount — объём одобренных.

month — YYYY-MM.

Решение

Условная агрегация

SELECT
  TO_CHAR(trans_date, 'YYYY-MM') AS month,
  country,
  COUNT(*) AS trans_count,
  SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
  SUM(amount) AS trans_total_amount,
  SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY TO_CHAR(trans_date, 'YYYY-MM'), country
ORDER BY month, country;

В MySQL: DATE_FORMAT(trans_date, '%Y-%m').

FILTER (Postgres 9.4+) — чище

SELECT
  TO_CHAR(trans_date, 'YYYY-MM') AS month,
  country,
  COUNT(*) AS trans_count,
  COUNT(*) FILTER (WHERE state = 'approved') AS approved_count,
  SUM(amount) AS trans_total_amount,
  SUM(amount) FILTER (WHERE state = 'approved') AS approved_total_amount
FROM Transactions
GROUP BY 1, 2;

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

1. country может быть NULL. GROUP BY с NULL объединит все NULL в одну группу — это поведение по спецификации; в задаче не критично, но в проде проверяйте.
2. SUM(amount) FILTER WHERE state='declined' если бы попросили. Подвох: SUM по пустому множеству — NULL, а не 0. Если надо 0 — COALESCE(SUM(...) FILTER..., 0).
3. Формат месяца. В MySQL DATE_FORMAT, в Postgres TO_CHAR, в SQL Server FORMAT(trans_date, 'yyyy-MM'). Разные диалекты.

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

Условная агрегация: SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) для счётчиков и THEN amount ELSE 0 для сумм. В Postgres лучше FILTER (WHERE ...).