1843. 可疑银行账户（难度：中等）

题目描述

（通过次数2,270 | 提交次数5,029，通过率45.14%）

表: Accounts
+----------------+------+
| Column Name    | Type |
+----------------+------+
| account_id     | int  |
| max_income     | int  |
+----------------+------+
account_id 是表主键。
每行包含一个银行账户每月最大收入的信息。

表: Transactions
+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| transaction_id | int      |
| account_id     | int      |
| type           | ENUM     |
| amount         | int      |
| day            | datetime |
+----------------+----------+
transaction_id 是表的主键。
每行包含转账信息。
type 是枚举类型（包含'Creditor','Debtor'），其中'Creditor'表示用户向其账户存入资金，'Debtor'表示用户从其账户取出资金。
amount 是转账的存取金额。

写一个SQL查询语句列示所有的可疑账户。
如果一个账户在连续两个及以上月份中总收入超过最大收入（max_income），那么这个账户可疑。账户当月总收入是当月存入资金总数（即transactions 表中type字段的'Creditor'）。
返回的结果表以transaction_id排序。

查询结果格式如下。
示例 1:
输入：
Accounts 表:
+------------+------------+
| account_id | max_income |
+------------+------------+
| 3          | 21000      |
| 4          | 10400      |
+------------+------------+
Transactions 表:
+----------------+------------+----------+--------+---------------------+
| transaction_id | account_id | type     | amount | day                 |
+----------------+------------+----------+--------+---------------------+
| 2              | 3          | Creditor | 107100 | 2021-06-02 11:38:14 |
| 4              | 4          | Creditor | 10400  | 2021-06-20 12:39:18 |
| 11             | 4          | Debtor   | 58800  | 2021-07-23 12:41:55 |
| 1              | 4          | Creditor | 49300  | 2021-05-03 16:11:04 |
| 15             | 3          | Debtor   | 75500  | 2021-05-23 14:40:20 |
| 10             | 3          | Creditor | 102100 | 2021-06-15 10:37:16 |
| 14             | 4          | Creditor | 56300  | 2021-07-21 12:12:25 |
| 19             | 4          | Debtor   | 101100 | 2021-05-09 15:21:49 |
| 8              | 3          | Creditor | 64900  | 2021-07-26 15:09:56 |
| 7              | 3          | Creditor | 90900  | 2021-06-14 11:23:07 |
+----------------+------------+----------+--------+---------------------+
输出：
+------------+
| account_id |
+------------+
| 3          |
+------------+
解释：
对于账户 3：
- 在 2021年6月，用户收入为 107100 + 102100 + 90900 = 300100。
- 在 2021年7月，用户收入为 64900。
可见收入连续两月超过21000的最大收入，因此账户3列入结果表中。

对于账户 4：
- 在 2021年5月，用户收入为 49300。
- 在 2021年6月，用户收入为 10400。
- 在 2021年7月，用户收入为 56300。
可见收入在5月与7月超过了最大收入，但6月没有。因为账户没有没有连续两月超过最大收入，账户4不列入结果表中。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/suspicious-bank-accounts

//测试数据
Create table If Not Exists Accounts (account_id int, max_income int);
Create table If Not Exists Transactions (transaction_id int, account_id int, type ENUM('creditor', 'debtor'), amount int, day datetime);

insert into Accounts (account_id, max_income) values ('3', '21000');
insert into Accounts (account_id, max_income) values ('4', '10400');

insert into Transactions (transaction_id, account_id, type, amount, day) values ('2', '3', 'Creditor', '107100', '2021-06-02 11:38:14');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('4', '4', 'Creditor', '10400', '2021-06-20 12:39:18');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('11', '4', 'Debtor', '58800', '2021-07-23 12:41:55');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('1', '4', 'Creditor', '49300', '2021-05-03 16:11:04');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('15', '3', 'Debtor', '75500', '2021-05-23 14:40:20');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('10', '3', 'Creditor', '102100', '2021-06-15 10:37:16');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('14', '4', 'Creditor', '56300', '2021-07-21 12:12:25');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('19', '4', 'Debtor', '101100', '2021-05-09 15:21:49');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('8', '3', 'Creditor', '64900', '2021-07-26 15:09:56');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('7', '3', 'Creditor', '90900', '2021-06-14 11:23:07');

解题思路

Accounts表保存了所有的账户以及账户合法的最大月收入。
Transactions表保存了账户的资金存入取出记录。如果某个账户，月累计存入资金超过了该账户合法的最大月收入，则该账户为非正常收入账户。如果一个账户连续2个月或以上都为非正常收入账户，则该账户可疑。
那么，可以按如下步骤实现题目的要求：
第一步：统计每个账户每个月的累计存入资金；
使用GROUP BY+SUM即可实现。
第二步：筛选出每个月的非正常收入账户；
可以直接和Accounts表关联，筛选出月累计存入资金超过了该账户合法的最大月收入的记录。
第三步：筛选出连续2个月或以上的非正常收入账户；
连续N次满足某个条件的场景，可以参考推文《单挑力扣（LeetCode）SQL笔试题：1454. 活跃用户（难度：困难）》。
本题目仅要求查询连续2个月，可以使用INNER JOIN自关联的方式，将当月与上月的数据关联到一行。如果能关联上，则表示当月与上月都是非正常收入账户。
当然，返回时需要考虑重复账户，使用DISTINCT关键字去重即可。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with
tmp1 as (
    select
        date_format(day,'%Y-%m-01') mon,
        account_id,
        sum(amount) amount
    from Transactions
    where type = 'Creditor'
    group by date_format(day,'%Y-%m-01'),
        account_id
),
tmp2 as (
    select
        a.mon,
        a.account_id
    from tmp1 a
    inner join Accounts b
    on a.account_id = b.account_id
    and a.amount > b.max_income
)
select
    distinct a.account_id
from tmp2 a
inner join tmp2 b
on date_sub(a.mon, interval 1 month)=b.mon
and a.account_id = b.account_id;