LeetCode 1205 每月交易2(PostgreSQL)

数据准备

create type state as enum('approved', 'declined');
create table Transactions(
id int, 
country varchar(4),
state_enum state,
amount int,
trans_date date
);
Create table If Not Exists Chargebacks (
trans_id int, 
trans_date date
);
insert into Transactions (id, country, state_enum, amount, trans_date) values ('101', 'US', 'approved', '1000', '2019-05-18');
insert into Transactions (id, country, state_enum, amount, trans_date) values ('102', 'US', 'declined', '2000', '2019-05-19');
insert into Transactions (id, country, state_enum, amount, trans_date) values ('103', 'US', 'approved', '3000', '2019-06-10');
insert into Transactions (id, country, state_enum, amount, trans_date) values ('104', 'US', 'declined', '4000', '2019-06-13');
insert into Transactions (id, country, state_enum, 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');

需求

编写一个 SQL 查询，以查找每个月和每个国家/地区的信息：已批准交易的数量及其总金额、退单的数量及其总金额。

输入

select * from transactions;
select * from chargebacks;

输出

1.每个月和每个国家/地区的已批准交易的数量

select id,country,state_enum ,amount,to_char(trans_date,'YYYY-MM')  as month,0 as tag
from Transactions
where state_enum  = 'approved';

2.每个月和每个国家/地区的退单的数量

select id,country,state_enum ,amount,to_char(c.trans_date,'YYYY-MM') as month, 1 as tag
from transactions t , chargebacks c 
where  t.id =c.trans_id ;

3.拼接上方两个查询结果,并计算所求

with t1 as(
	select id,country,state_enum ,amount,to_char(trans_date,'YYYY-MM')  as month,0 as tag
	from Transactions
	where state_enum  = 'approved'
	union all 
	select id,country,state_enum ,amount,to_char(c.trans_date,'YYYY-MM') as month, 1 as tag
	from transactions t , chargebacks c 
	where  t.id =c.trans_id
)
select month,country,
	--在 PostgreSQL 中，不支持在聚合函数的参数中直接使用 IF 函数
	count(case when state_enum='approved' and tag=0 then 1 else null end) as approved_count,
	sum(case when state_enum='approved' and tag=0 then amount else 0 end) as approved_amount,
	count(case when tag=1 then 1 else null end) as chargeback_count,
	sum(case when tag=1 then amount else 0 end) as chargeback_amount
from t1
group by month,country
order by month;