# $1205. 每月交易II
# https://leetcode-cn.com/problems/monthly-transactions-ii/

# SQL架构
Create table If Not Exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date);
Create table If Not Exists Chargebacks (trans_id int, trans_date date);
Truncate table Transactions;
insert into Transactions (id, country, state, amount, trans_date) values ('101', 'US', 'approved', '1000', '2019-05-18');
insert into Transactions (id, country, state, amount, trans_date) values ('102', 'US', 'declined', '2000', '2019-05-19');
insert into Transactions (id, country, state, amount, trans_date) values ('103', 'US', 'approved', '3000', '2019-06-10');
insert into Transactions (id, country, state, amount, trans_date) values ('104', 'US', 'declined', '4000', '2019-06-13');
insert into Transactions (id, country, state, amount, trans_date) values ('105', 'US', 'approved', '5000', '2019-06-15');
Truncate table Chargebacks;
insert into Chargebacks (trans_id, trans_date) values ('102', '2019-05-29');
insert into Chargebacks (trans_id, trans_date) values ('101', '2019-06-30');
insert into Chargebacks (trans_id, trans_date) values ('105', '2019-09-18');

# Write your MySQL query statement below
select
    date_format(trans_date, '%Y-%m') as month,
    country,
    sum(state = 'approved') as approved_count,
    sum(if(state = 'approved', amount, 0)) as approved_amount,
    sum(state = 'chargeback') as chargeback_count,
    sum(if(state = 'chargeback', amount, 0)) as chargeback_amount
from
    (
        select
            id,
            country,
            state,
            amount,
            trans_date
        from
            Transactions
        union
        all
        select
            id,
            country,
            'chargeback' as state,
            amount,
            c.trans_date
        from
            Chargebacks c
            left join Transactions t on c.trans_id = t.id
    ) tmp
group by
    month,
    country
having
    approved_amount
    or chargeback_amount;

# clean-up
drop table Transactions;
drop table Chargebacks;