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 DetectionTransactionsaccounttypeamountmonth1Credit2500Jan1Credit2000Jan1Credit3000FebGROUP BYMonthly Totalsaccountmonthincome1Jan45001Feb3000LAG()Resultaccount_id1Account 1: Income exceeds limit (4000) in consecutive monthsJan: 4500 > 4000 ✗ | Feb: 3000 < 4000 but consecutive pattern detected
Understanding the Visualization
1
Group
Calculate monthly income totals per account
2
Compare
Check if income exceeds max_income
3
Detect
Find consecutive violations with LAG()
Key Takeaway
🎯 Key Insight: Use LAG() window function to compare current month violations with previous month violations to find consecutive patterns

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