#1205.每月交易 ||

这题要求找出每个月每个国家的交易金额和数量,和退单数量与金额,而交易记录属于Transactions表,而退单记录属于Chargebacks表,可以单独按照国家和月份计算交易的金额和数量,再计算出每月每个国家的退单数量和金额,使用union连接,即

 select date_format(trans_date,'%Y-%m') month,country,

count(case when state='approved' then 1 end) approved_count,

sum(case when state='approved' then amount end) approved_amount,

null as chargeback_count,

null as chargeback_amount

from Transactions

group by date_format(trans_date,'%Y-%m'),country having approved_amount is not null

union

select date_format(c.trans_date,'%Y-%m') month,

country,

null as approved_count,

null as approved_amount,

count(1) chargeback_count,sum(amount) chargeback_amount

from Transactions t inner join chargebacks c

on t.id = c.trans_id group by date_format(c.trans_date,'%Y-%m'),country

因为要求忽略所有的0行,所以要提前筛选掉所有值都是的null的行

现在将所有数据按照月份和国家进行分组聚合,

with a as

(

    select date_format(trans_date,'%Y-%m') month,country,

count(case when state='approved' then 1 end) approved_count,

sum(case when state='approved' then amount end) approved_amount,

null as chargeback_count,

null as chargeback_amount

from Transactions

group by date_format(trans_date,'%Y-%m'),country having approved_amount is not null

union

select date_format(c.trans_date,'%Y-%m') month,

country,

null as approved_count,

null as approved_amount,

count(1) chargeback_count,sum(amount) chargeback_amount

from Transactions t inner join chargebacks c

on t.id = c.trans_id group by date_format(c.trans_date,'%Y-%m'),country)

select month,country,ifnull(sum(approved_count),0) approved_count,ifnull(sum(approved_amount),0) approved_amount,ifnull(sum(chargeback_count),0) chargeback_count,ifnull(sum(chargeback_amount),0) chargeback_amount from a group by month,country ;就可以得出所有结果