# 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。

```sql
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

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





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");
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://zqt0.gitbook.io/leetcode/sql/1205.monthly-transactions-ii.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.