Accounts with Consecutive Income Breaches

MEDIUM20 pts

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

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

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

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).

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Accounts =

[{"account_id":3,"max_income":21000},{"account_id":4,"max_income":10400}]

Transactions =

[{"transaction_id":2,"account_id":3,"type":"Creditor","amount":107100,"day":"2021-06-02 11:38:14"},{"transaction_id":1,"account_id":4,"type":"Creditor","amount":49300,"day":"2021-05-03 16:11:04"},{"transaction_id":8,"account_id":3,"type":"Creditor","amount":64900,"day":"2021-07-26 15:09:56"},{"transaction_id":7,"account_id":3,"type":"Creditor","amount":90900,"day":"2021-06-14 11:23:07"},{"transaction_id":11,"account_id":4,"type":"Debtor","amount":58800,"day":"2021-07-23 12:41:55"},{"transaction_id":4,"account_id":4,"type":"Creditor","amount":10400,"day":"2021-06-20 12:39:18"},{"transaction_id":10,"account_id":3,"type":"Creditor","amount":102100,"day":"2021-06-15 10:37:16"},{"transaction_id":14,"account_id":4,"type":"Creditor","amount":56300,"day":"2021-07-21 12:12:25"}]

Accounts with Consecutive Income Breaches

Hints

Accounts with Consecutive Income Breaches

Hints