Accounts with Consecutive Income Breaches

Medium

A financial monitoring service tracks account-level transaction activity and monthly risk limits. Table: Accounts account_id: unique account identifier max_income: maximum expected monthly incoming amount for that account Table: Transactions transaction_id: unique transaction identifier account_id: account receiving or sending funds type: either "Creditor" (incoming money) or "Debtor" (outgoing money) amount: transaction amount day: timestamp of the transaction Business rule: An account is considered high-risk if its total monthly incoming amount (sum of Creditor amounts) exceeds max_income in at least two consecutive calendar months. Task: Return the IDs of all accounts that satisfy this risk rule. Output requirements: Return exactly one column named account_id. Each account_id should appear at most once. Output order may be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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 |
| 10             | 3          | Creditor | 102100 | 2021-06-15 10:37:16 |
| 7              | 3          | Creditor | 90900  | 2021-06-14 11:23:07 |
| 8              | 3          | Creditor | 64900  | 2021-07-26 15:09:56 |
| 1              | 4          | Creditor | 49300  | 2021-05-03 16:11:04 |
| 4              | 4          | Creditor | 10400  | 2021-06-20 12:39:18 |
| 14             | 4          | Creditor | 56300  | 2021-07-21 12:12:25 |
| 11             | 4          | Debtor   | 58800  | 2021-07-23 12:41:55 |

Output

[
  {"account_id":3}
]

Explanation

Account 3 exceeds its limit in June and July consecutively. Account 4 exceeds in May and July but not in June, so it is not flagged.

Example 2

Input

Accounts:
| account_id | max_income |
|------------|------------|
| 1          | 5000       |
| 2          | 9000       |

Transactions:
| transaction_id | account_id | type     | amount | day                 |
|----------------|------------|----------|--------|---------------------|
| 1              | 1          | Creditor | 5000   | 2024-01-11 10:00:00 |
| 2              | 1          | Creditor | 4900   | 2024-02-11 10:00:00 |
| 3              | 2          | Debtor   | 2000   | 2024-01-20 09:00:00 |
| 4              | 2          | Creditor | 8000   | 2024-03-02 12:00:00 |

Output

[]

Explanation

No account exceeds max_income in two consecutive months. Equality with max_income is not considered an exceed event.

Example 3

Input

Accounts:
| account_id | max_income |
|------------|------------|
| 10         | 12000      |
| 11         | 5000       |

Transactions:
| transaction_id | account_id | type     | amount | day                 |
|----------------|------------|----------|--------|---------------------|
| 101            | 10         | Creditor | 13000  | 2020-12-04 08:00:00 |
| 102            | 10         | Creditor | 15000  | 2021-01-09 08:30:00 |
| 103            | 11         | Creditor | 6000   | 2021-01-02 08:30:00 |
| 104            | 11         | Creditor | 4000   | 2021-02-02 08:30:00 |

Output

[
  {"account_id":10}
]

Explanation

Consecutive-month detection must work across year boundaries (December to January).

Constraints

1 <= number of rows in Accounts <= 250000
0 <= number of rows in Transactions <= 400000
Accounts.account_id values are unique
Transactions.transaction_id values are unique
Transactions.type is one of: Creditor, Debtor
0 <= max_income <= 1000000000
0 <= amount <= 1000000000
day is a valid date or datetime value
An exceed event means monthly Creditor sum strictly greater than max_income
Return exactly one column: account_id
Each account_id should appear once at most
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column account_id

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 | | 10 | 3 | Creditor | 102100 | 2021-06-15 10:37:16 | | 7 | 3 | Creditor | 90900 | 2021-06-14 11:23:07 | | 8 | 3 | Creditor | 64900 | 2021-07-26 15:09:56 | | 1 | 4 | Creditor | 49300 | 2021-05-03 16:11:04 | | 4 | 4 | Creditor | 10400 | 2021-06-20 12:39:18 | | 14 | 4 | Creditor | 56300 | 2021-07-21 12:12:25 | | 11 | 4 | Debtor | 58800 | 2021-07-23 12:41:55 |

Accounts: | account_id | max_income | |------------|------------| | 1 | 5000 | | 2 | 9000 | Transactions: | transaction_id | account_id | type | amount | day | |----------------|------------|----------|--------|---------------------| | 1 | 1 | Creditor | 5000 | 2024-01-11 10:00:00 | | 2 | 1 | Creditor | 4900 | 2024-02-11 10:00:00 | | 3 | 2 | Debtor | 2000 | 2024-01-20 09:00:00 | | 4 | 2 | Creditor | 8000 | 2024-03-02 12:00:00 |

Accounts: | account_id | max_income | |------------|------------| | 10 | 12000 | | 11 | 5000 | Transactions: | transaction_id | account_id | type | amount | day | |----------------|------------|----------|--------|---------------------| | 101 | 10 | Creditor | 13000 | 2020-12-04 08:00:00 | | 102 | 10 | Creditor | 15000 | 2021-01-09 08:30:00 | | 103 | 11 | Creditor | 6000 | 2021-01-02 08:30:00 | | 104 | 11 | Creditor | 4000 | 2021-02-02 08:30:00 |