1205.(Medium)每月交易II

Transactions 记录表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
状态列是类型为 [approved（已批准）、declined（已拒绝）] 的枚举。

Chargebacks 表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| charge_date    | date    |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息。
trans_id 是 transactions 表的 id 列的外键。
每项退单都对应于之前进行的交易，即使未经批准。

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

注意：在您的查询中，给定月份和国家，忽略所有为零的行。

查询结果格式如下所示：

Transactions 表：
+------+---------+----------+--------+------------+
| 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      | declined | 4000   | 2019-06-13 |
| 105  | US      | approved | 5000   | 2019-06-15 |
+------+---------+----------+--------+------------+

Chargebacks 表：
+------------+------------+
| trans_id   | trans_date |
+------------+------------+
| 102        | 2019-05-29 |
| 101        | 2019-06-30 |
| 105        | 2019-09-18 |
+------------+------------+

Result 表：
+----------+---------+----------------+-----------------+-------------------+--------------------+
| month    | country | approved_count | approved_amount | chargeback_count  | chargeback_amount  |
+----------+---------+----------------+-----------------+-------------------+--------------------+
| 2019-05  | US      | 1              | 1000            | 1                 | 2000               |
| 2019-06  | US      | 2              | 8000            | 1                 | 1000               |
| 2019-09  | US      | 0              | 0               | 1                 | 5000               |
+----------+---------+----------------+-----------------+-------------------+--------------------+

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/monthly-transactions-ii

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

记得剔除“declined”相关行，否则结果会出现某月某国四个数据全是0。

SELECT LEFT(trans_date,7) `month`
    , country
    , sum(state = 'approved') approved_count
    , sum(if(state = 'approved',amount,0)) approved_amount
    , sum(state = 'chargeback') chargeback_count 
    , sum(if(state = 'chargeback',amount,0)) chargeback_amount
FROM
(
    SELECT * FROM Transactions
    union 
    SELECT trans_id, Country, 'chargeback' state, amount, c.trans_date FROM Chargebacks c LEFT JOIN Transactions t on c.trans_id=t.id
) a
where state != 'declined'
GROUP BY LEFT(trans_date,7), country;

Schema

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');





insert into Transactions (id, country, state, amount, trans_date) values ('100',"CB","declined",'4000',"2019-02-04");
insert into Transactions (id, country, state, amount, trans_date) values ('101',"BB","approved",'7000',"2019-02-17");
insert into Transactions (id, country, state, amount, trans_date) values ('102',"CA","declined",'6000',"2019-02-26");
insert into Transactions (id, country, state, amount, trans_date) values ('103',"AA","declined",'7000',"2019-04-01");

insert into Chargebacks (trans_id, trans_date) values ('100', "2019-03-29");
insert into Chargebacks (trans_id, trans_date) values ('102', "2019-02-28");
insert into Chargebacks (trans_id, trans_date) values ('103', "2019-05-09");