Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1205.html
1205. Monthly Transactions II
Level
Medium
Description
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"].
Table: Chargebacks
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| trans_id | int |
| charge_date | date |
+----------------+---------+
Chargebacks contains basic information regarding incoming chargebacks from some transactions placed in Transactions table.
trans_id is a foreign key to the id column of Transactions table.
Each chargeback corresponds to a transaction made previously even if they were not approved.
Write an SQL query to find for each month and country, the number of approved transactions and their total amount, the number of chargebacks and their total amount.
Note: In your query, given the month and country, ignore rows with all zeros.
The query result format is in the following example:
Transactions table:
+------+---------+----------+--------+------------+
| id | country | state | amount | trans_date |
+------+---------+----------+--------+------------+
| 101 | US | approved | 1000 | 2019-05-18 |
| 102 | US | declined | 2000 | 2019-05-19 |
| 103 | US | approved | 3000 | 2019-06-10 |
| 104 | US | approved | 4000 | 2019-06-13 |
| 105 | US | approved | 5000 | 2019-06-15 |
+------+---------+----------+--------+------------+
Chargebacks table:
+------------+------------+
| trans_id | trans_date |
+------------+------------+
| 102 | 2019-05-29 |
| 101 | 2019-06-30 |
| 105 | 2019-09-18 |
+------------+------------+
Result table:
+----------+---------+----------------+-----------------+-------------------+--------------------+
| month | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
+----------+---------+----------------+-----------------+-------------------+--------------------+
| 2019-05 | US | 1 | 1000 | 1 | 2000 |
| 2019-06 | US | 3 | 12000 | 1 | 1000 |
| 2019-09 | US | 0 | 0 | 1 | 5000 |
+----------+---------+----------------+-----------------+-------------------+--------------------+
Solution
Use date_format(trans_date, '%Y-%m') to obtain the month of each transaction.
For approved transactions and chargeback transactions, select the entries respectively. As for chargeback transactions, do inner join on tables Transactions and Chargebacks using id in Transactions and trans_id in Chargebacks. Use union all to union the selected results. Obtain the values of the required fields as the final result.
# Write your MySQL query statement below
select month, country, sum(if(state = 'approved', 1, 0)) as approved_count,
sum(if(state = 'approved', amount, 0)) as approved_amount,
sum(if(state = 'chargeback', 1, 0)) as chargeback_count,
sum(if(state = 'chargeback', amount, 0)) as chargeback_amount
from ((
select date_format(trans_date, '%Y-%m') as month, country, amount, 'approved' as state
from Transactions
where state = 'approved'
) union all (
select date_format(Chargebacks.trans_date, '%Y-%m') as month, country, amount, 'chargeback' as state
from Transactions inner join Chargebacks on Transactions.id = Chargebacks.trans_id
)) temp
group by temp.month, temp.country;