Monthly-Transactions-I.md

Table: Transactions

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"].

Problem:

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 given in the example.

Example 1:

Input:

Transactions table:

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

Solution

Approach 1: Using CASE WHEN

• We use DATE_FORMAT to convert the 'trans_date' column into YYYY-MM format.
• '%Y-%m' uses a four-digit year and two-digit month. Example: '2018-05'
• Along with aggregate functions like COUNT and SUM, we use CASE WHEN OR IF to add a condition for the status.
• Lastly, the results are grouped and ordered by month and country

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(state) AS trans_count,
    COUNT(CASE WHEN status = 'approved' THEN 1 ELSE 0) AS approved_count,
    SUM(amount) AS trans_total_amount, 
    SUM(CASE WHEN status = 'approved' THEN amount ELSE 0) AS approved_total_amount
FROM Transactions 
GROUP BY DATE_FORMAT(trans_date, '%Y-%M'), country
ORDER BY month, country;

Approach 2: Using IF

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(state) AS trans_count,
    COUNT(IF(status = 'approved', 1, 0)) AS approved_count,
    SUM(amount) AS trans_total_amount, 
    SUM(IF(status = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions 
GROUP BY DATE_FORMAT(trans_date, '%Y-%M'), country
ORDER BY month, country;

---