Last updated on March 10th, 2025 at 10:50 pm

Here, we see a Monthly Transactions I LeetCode Solution. This Leetcode problem is solved using MySQL and Pandas.

List of all LeetCode Solution

Level of Question

Medium

Monthly Transactions I LeetCode Solution

1. Problem Statement

Column Name	Type
id	int
country	varchar
state	enum
amount	int
trans_date	date

id is the primary key of this table. The table has information about incoming transactions. The state column is an enum of type [“approved”, “declined”].

Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

Return the result table in any order.

The query result format is in the following example.

Example 1:
Input:

id	country	state	amount	trans_date
121	US	approved	1000	2018-12-18
122	US	declined	2000	2018-12-19
123	US	approved	2000	2019-01-01
124	DE	approved	2000	2019-01-07

Output:

month	country	trans_count	approved_count	trans_total_amount	approved_total_amount
2018-12	US	2	1	3000	1000
2019-01	US	1	1	2000	2000
2019-01	DE	1	1	2000	2000

2. Code Implementation in Different Languages

2.1 Monthly Transactions I MySQL

select 
  date_format(trans_date, '%Y-%m') as month, 
  country, 
  count(*) as trans_count, 
  sum(
    if(state = 'approved', 1, 0)
  ) as approved_count, 
  sum(amount) as trans_total_amount, 
  sum(
    if(state = 'approved', amount, 0)
  ) as approved_total_amount 
from 
  Transactions 
group by 
  date_format(trans_date, '%Y-%m'), 
  country;