Monthly Transactions II Problem


Description

LeetCode Problem 1205.

Table: Transactions

1
2
3
4
5
6
7
8
9
10
11
12
+----------------+---------+
| 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

1
2
3
4
5
6
7
8
9
+----------------+---------+
| 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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               |
+----------+---------+----------------+-----------------+-------------------+--------------------+


MySQL Solution

1
2
3
4
5
6
7
8
9
10
11
12
select  date_format(trans_date, '%Y-%m') as month, country, 
        sum(case when state = 'approved' then 1 else 0 end) as approved_count,
        sum(case when state = 'approved' then amount else 0 end) as approved_amount,
        sum(case when state = 'chargeback' then 1 else 0 end) as chargeback_count,
        sum(case when state = 'chargeback' then amount else 0 end) as chargeback_amount
from(select  c.trans_id, t.country, 'chargeback' as state, t.amount, c.trans_date
    from Chargebacks as c join Transactions t on c.trans_id = t.id
    union all
    select *
    from Transactions) as t1
group by country, month
having approved_amount > 0 or chargeback_amount > 0




Related Posts

Report Contiguous Dates Problem

LeetCode 1225. Write an SQL query to generate a report...

Monthly Transactions II Problem

LeetCode 1205. Write an SQL query to find for each...

Team Scores In Football Tournament Problem

LeetCode 1212. Write an SQL query that selects the team_id,...

Last Person To Fit In The Elevator Problem

LeetCode 1204. Write an SQL query to find the person_name...

Queries Quality And Percentage Problem

LeetCode 1211. Write an SQL query to find each query_name,...

Number Of Comments Per Post Problem

LeetCode 1241. Write an SQL query to find number of...