1193. Monthly Transactions I

SQL 50 · Basic Aggregate Functions (day 3). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

MediumSolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One (month, country).

2. Approach

DATE_FORMAT(trans_date, '%Y-%m'), GROUP BY month, country, with COUNT, SUM(state = 'approved') and two SUMs of amount.TO_CHAR(trans_date, 'YYYY-MM'), GROUP BY month, country, with COUNT(*), COUNT(*) FILTER (WHERE state = 'approved') and two SUMs of amount.

3. The trap

Approved amount: SUM(CASE WHEN approved THEN amount ELSE 0 END), not a WHERE.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country,
  COUNT(*) AS trans_count,
  SUM(state = 'approved') 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 month, country;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(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY month, country;

Patterns used

Decode a question · Toolbox · MySQL vs PostgreSQL