1843.(Medium)Suspicious Bank Accounts

Table: Accounts

+----------------+------+
| Column Name    | Type |
+----------------+------+
| account_id     | int  |
| max_income     | int  |
+----------------+------+
account_id is the primary key for this table.
Each row contains information about the maximum monthly income for one bank account.

Table: Transactions

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| transaction_id | int      |
| account_id     | int      |
| type           | ENUM     |
| amount         | int      |
| day            | datetime |
+----------------+----------+
transaction_id is the primary key for this table.
Each row contains information about one transaction.
type is ENUM ('Creditor','Debtor') where 'Creditor' means the user deposited money into their account and 'Debtor' means the user withdrew money from their account.
amount is the amount of money depositied/withdrawn during the transaction.

Write an SQL query to report the IDs of all suspicious bank accounts.

A bank account is suspicious if the total income exceeds the max_income for this account for two or more consecutive months. The total income of an account in some month is the sum of all its deposits in that month (i.e., transactions of the type 'Creditor').

Return the result table in ascending order by transaction_id.

The query result format is in the following example:

Accounts table:
+------------+------------+
| account_id | max_income |
+------------+------------+
| 3          | 21000      |
| 4          | 10400      |
+------------+------------+

Transactions table:
+----------------+------------+----------+--------+---------------------+
| 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 |
+----------------+------------+----------+--------+---------------------+

Result table:
+------------+
| account_id |
+------------+
| 3          |
+------------+

For account 3:
- In 6-2021, the user had an income of 107100 + 102100 + 90900 = 300100.
- In 7-2021, the user had an income of 64900.
We can see that the income exceeded the max income of 21000 for two consecutive months, so we include 3 in the result table.

For account 4:
- In 5-2021, the user had an income of 49300.
- In 6-2021, the user had an income of 10400.
- In 7-2021, the user had an income of 56300.
We can see that the income exceeded the max income in May and July, but not in June. Since the account did not exceed the max income for two consecutive months, we do not include it in the result table.

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/suspicious-bank-accounts

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

Solution

刚开始用lag窗口函数，没考虑到有些account的月份之间有间隔。

-- ❌❌❌ 
SELECT DISTINCT account_id
FROM (
    SELECT *
        , exceed+LAG(exceed,1) over (PARTITION BY account_id ORDER BY mon) exceed2
    FROM (
        SELECT account_id, left(day,7) mon 
            ,sum(amount)>max(max_income)  exceed 
        from Transactions LEFT JOIN Accounts USING(account_id)
        where type='Creditor'
        GROUP BY account_id,left(day,7)
        order by 1,2
    ) a 
)b WHERE exceed2=2;

正确做法是用 PERIOD_DIFF(yyyymm1,yyyymm2),p2)=1 函数self join 真正连续的两个月。

-- ✅✅✅
with a as (
    SELECT account_id, DATE_FORMAT(day,'%Y%m') mon 
        ,sum(amount)>max(max_income)  exceed 
    from Transactions LEFT JOIN Accounts USING(account_id)
    where type='Creditor'
    GROUP BY account_id, DATE_FORMAT(day,'%Y%m')
    order by 1,2
)
SELECT DISTINCT a1.account_id 
FROM a a1, a a2 
where a1.account_id = a2.account_id and PERIOD_DIFF(a1.mon,a2.mon)=1 and a1.exceed + a2.exceed =2

Schema

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);
Truncate table Accounts;
insert into Accounts (account_id, max_income) values ('3', '21000');
insert into Accounts (account_id, max_income) values ('4', '10400');
Truncate table Transactions;
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');
------
Truncate table Accounts;
Truncate table Transactions;
insert into Accounts (account_id, max_income) values (15, 12000); 
insert into Accounts (account_id, max_income) values (2, 3000); 
insert into Accounts (account_id, max_income) values (4, 32000); 
insert into Accounts (account_id, max_income) values (16, 7000); 
insert into Accounts (account_id, max_income) values (9, 19000); 
insert into Accounts (account_id, max_income) values (10, 49000); 
insert into Accounts (account_id, max_income) values (3, 58000); 
insert into Accounts (account_id, max_income) values (14, 18000);
insert into Transactions (transaction_id, account_id, type, amount, day) values (58, 9, "Debtor", 34500, "2021-06-18 17:52:31"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (66, 2, "Creditor", 12100, "2021-05-07 17:14:09"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (43, 15, "Debtor", 79600, "2021-06-19 13:09:17"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (9, 3, "Creditor", 28600, "2021-05-30 11:33:10"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (29, 9, "Debtor", 2700, "2021-07-08 15:25:40"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (70, 2, "Creditor", 104200, "2021-06-07 17:40:02"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (32, 3, "Debtor", 3400, "2021-06-07 14:42:57"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (33, 3, "Creditor", 32700, "2021-05-08 10:16:16"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (25, 3, "Debtor", 37700, "2021-07-19 17:15:39"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (1, 2, "Debtor", 108600, "2021-06-24 09:50:52"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (65, 9, "Debtor", 107000, "2021-05-03 15:37:21"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (38, 15, "Debtor", 2000, "2021-05-17 15:41:58"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (27, 9, "Creditor", 61300, "2021-07-28 13:00:36"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (60, 9, "Debtor", 31800, "2021-06-13 09:33:50"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (40, 10, "Creditor", 81700, "2021-06-18 17:54:06"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (26, 3, "Debtor", 65900, "2021-05-11 17:53:27"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (3, 10, "Creditor", 50400, "2021-06-29 16:12:53"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (42, 16, "Creditor", 94800, "2021-05-27 17:16:32"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (55, 2, "Creditor", 77900, "2021-05-13 13:06:54"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (73, 3, "Debtor", 13000, "2021-06-17 11:18:41"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (54, 15, "Creditor", 24300, "2021-07-29 10:36:49"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (5, 4, "Debtor", 100900, "2021-07-29 13:37:09"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (44, 16, "Creditor", 23100, "2021-07-28 12:54:50"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (15, 10, "Creditor", 29600, "2021-06-18 10:02:07"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (74, 4, "Debtor", 78500, "2021-06-21 09:31:52"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (78, 2, "Creditor", 30300, "2021-07-22 09:49:31"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (64, 16, "Creditor", 90300, "2021-07-26 12:36:27"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (57, 9, "Debtor", 8600, "2021-05-13 12:19:53"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (53, 15, "Creditor", 13300, "2021-06-15 14:26:01"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (79, 16, "Debtor", 74000, "2021-05-29 09:16:28"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (46, 2, "Creditor", 96000, "2021-07-02 14:44:38"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (52, 3, "Creditor", 39800, "2021-07-23 15:31:01"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (51, 3, "Creditor", 55500, "2021-07-10 14:34:32"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (49, 15, "Debtor", 85200, "2021-06-18 12:52:55"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (12, 14, "Creditor", 106100, "2021-05-03 13:25:18"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (16, 9, "Debtor", 106000, "2021-05-21 09:39:47"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (56, 4, "Debtor", 96900, "2021-06-07 16:37:55"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (37, 9, "Creditor", 63300, "2021-06-19 17:04:54"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (45, 4, "Creditor", 77000, "2021-05-28 14:00:42"); 
insert into Transactions (transaction_id, account_id, type, amount, day) values (48, 2, "Creditor", 3900, "2021-07-01 16:15:16");

Previous1841.(Medium)League Statistics Next1853.(Easy)Convert Date Format

Last updated 2 years ago