Suspicious Bank Accounts - Problem

You are given two tables: Accounts and Transactions.

The Accounts table contains information about maximum monthly income for each bank account:

  • account_id - unique identifier for each account
  • max_income - maximum allowed monthly income

The Transactions table contains transaction details:

  • transaction_id - unique identifier for each transaction
  • account_id - account involved in the transaction
  • type - either 'Creditor' (deposit) or 'Debtor' (withdrawal)
  • amount - transaction amount
  • day - transaction date

A bank account is suspicious if the total income exceeds the max_income for two or more consecutive months. The total income is the sum of all 'Creditor' transactions in that month.

Write a SQL query to find all suspicious bank accounts.

Table Schema

Accounts
Column Name Type Description
account_id PK int Unique identifier for each bank account
max_income int Maximum allowed monthly income for this account
Primary Key: account_id
Transactions
Column Name Type Description
transaction_id PK int Unique identifier for each transaction
account_id int Account involved in the transaction
type ENUM Transaction type: 'Creditor' (deposit) or 'Debtor' (withdrawal)
amount int Transaction amount
day datetime Date when transaction occurred
Primary Key: transaction_id

Input & Output

Example 1 — Basic Consecutive Violations
Input Tables:
Accounts
account_id max_income
1 4000
2 3000
Transactions
transaction_id account_id type amount day
1 1 Creditor 2500 2021-01-02
2 1 Creditor 2000 2021-01-15
3 1 Creditor 3000 2021-02-05
4 1 Creditor 2000 2021-02-20
5 2 Creditor 2000 2021-01-10
Output:
account_id
1
💡 Note:

Account 1 has total income of 4500 in January (2500+2000) and 5000 in February (3000+2000), both exceeding the max_income of 4000 for consecutive months. Account 2 only has 2000 in January, which doesn't exceed its limit of 3000.
Example 2 — Non-consecutive Violations
Input Tables:
Accounts
account_id max_income
3 3000
Transactions
transaction_id account_id type amount day
6 3 Creditor 4000 2021-01-05
7 3 Creditor 1000 2021-02-10
8 3 Creditor 4000 2021-03-15
Output:
account_id
💡 Note:

Account 3 exceeds its limit in January (4000 > 3000) and March (4000 > 3000), but not in February (1000 < 3000). Since the violations are not consecutive, this account is not suspicious.

Constraints

  • 1 ≤ account_id ≤ 10000
  • 1 ≤ max_income ≤ 1000000
  • type is either 'Creditor' or 'Debtor'
  • 1 ≤ amount ≤ 100000
  • day is a valid datetime

Visualization

Tap to expand
Suspicious Bank Accounts INPUT DATA id name max_income 1 Alice 5000 2 Bob 8000 Transactions acc type amount month 1 Creditor 6000 2024-01 1 Creditor 5500 2024-02 1 Debtor 1000 2024-01 2 Creditor 7000 2024-01 2 Creditor 5000 2024-02 = Creditor (Income) = Debtor (Ignore) ALGORITHM STEPS 1 Filter Creditor Txns Keep only type='Creditor' 2 Group by Account+Month Sum deposits per month Account 1: Jan: 6000 > 5000 Feb: 5500 > 5000 Account 2: Jan: 7000 < 8000 Feb: 5000 < 8000 3 Find Consecutive Months Track months exceeding limit Acc 1: Jan-Feb (2 consec.) SUSPICIOUS! 4 Return Suspicious IDs Accounts with 2+ consec. FINAL RESULT Suspicious Accounts Found: Account #1 - Alice ! 2 consecutive months over max_income Account #2 - Bob OK - No violations All months within limit Output: [1] 1 suspicious account identified Key Insight: The algorithm aggregates Creditor transactions by account and month, compares totals against max_income, then uses a sliding window to detect 2+ consecutive violations. Only 'Creditor' type counts as income. Time: O(n log n) for sorting transactions by date. Space: O(n) for storing monthly summaries. TutorialsPoint - Suspicious Bank Accounts | Optimal Solution

Related Problems

Asked in
Amazon 15 Google 8 Microsoft 12
25.0K Views
Medium Frequency
~20 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen