Suspicious Bank Accounts - Practice Coding Problems

Suspicious Bank Accounts - Problem

Database Medium

Suspicious Bank Accounts Detection

You're working as a data analyst for a financial institution that needs to identify potentially suspicious bank accounts based on unusual income patterns.

The Problem:
A bank account becomes suspicious when its total monthly income exceeds the declared max_income for two or more consecutive months. This could indicate money laundering, tax evasion, or other fraudulent activities.

What You Have:
• Accounts table: Contains each account's ID and their declared maximum monthly income
• Transactions table: Contains all transactions with type ('Creditor' for deposits, 'Debtor' for withdrawals), amounts, and dates

Your Task:
Calculate the total monthly income (sum of all 'Creditor' transactions) for each account, then identify accounts where this total exceeds their max_income for 2+ consecutive months.

Example:
If Account 123 has max_income = 5000 but earned 6000 in January and 7000 in February, it's suspicious! 🚨

Input & Output

example_1.sql — Basic Suspicious Account

$ Input: Accounts: [[3, 21000], [4, 10400]] Transactions: [[2, 3, 'Creditor', 107100, '2021-06-02'], [3, 4, 'Creditor', 10900, '2021-06-02'], [4, 4, 'Creditor', 1000, '2021-06-02']]

› Output: [3]

💡 Note: Account 3 has max_income of 21000 but received 107100 in June 2021, exceeding the limit. However, we need to check if this continues for consecutive months to be truly suspicious.

example_2.sql — Multiple Consecutive Months

$ Input: Accounts: [[1, 5000], [2, 3000]] Transactions: [[1, 1, 'Creditor', 6000, '2021-01-15'], [2, 1, 'Creditor', 7000, '2021-02-15'], [3, 2, 'Creditor', 2000, '2021-01-15']]

› Output: [1]

💡 Note: Account 1: max_income=5000, but earned 6000 in Jan and 7000 in Feb (both exceed limit for 2 consecutive months = suspicious). Account 2: only 2000 in Jan, which is under the 3000 limit = not suspicious.

example_3.sql — Edge Case: Non-consecutive Violations

$ Input: Accounts: [[1, 1000]] Transactions: [[1, 1, 'Creditor', 2000, '2021-01-15'], [2, 1, 'Creditor', 500, '2021-02-15'], [3, 1, 'Creditor', 3000, '2021-03-15']]

› Output: []

💡 Note: Account 1 exceeded limits in Jan (2000 > 1000) and Mar (3000 > 1000), but Feb was within limits (500 < 1000). Since violations weren't consecutive, account is not suspicious.

Visualization

Tap to expand

Understanding the Visualization

Monthly Income Calculation

Sum all 'Creditor' transactions per account per month

Limit Comparison

Compare monthly totals against max_income for each account

Consecutive Detection

Use window functions to check if violations occur in adjacent months

Suspicious Flagging

Mark accounts with 2+ consecutive monthly violations as suspicious

Key Takeaway

🎯 Key Insight: SQL window functions like LAG() allow us to compare current month with previous month in a single query, making consecutive violation detection both elegant and efficient!

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass through data with sorting for window functions

⚡ Linearithmic

Space Complexity

O(k)

Only stores final result set, k = number of suspicious accounts

✓ Linear Space

Constraints

1 ≤ account_id ≤ 10⁵
1 ≤ max_income ≤ 10⁶
1 ≤ transaction_id ≤ 10⁶
1 ≤ amount ≤ 10⁵
type is either 'Creditor' or 'Debtor'
day is in format 'YYYY-MM-DD HH:MM:SS'

Asked in

J JPMorgan 45 G Goldman Sachs 38 B Bank of America 32 W Wells Fargo 28 C Citi 25

The optimal solution uses SQL window functions with a single CTE to efficiently calculate monthly income and detect consecutive violations. Key insight: LAG/LEAD functions eliminate the need for self-joins when checking adjacent months, reducing complexity from O(n²) to O(n log n).

Common Approaches

Approach	Time	Space	Notes
✓ Single CTE with Lead/Lag (Optimal)	O(n log n)	O(k)	Optimal solution using single CTE with window functions to detect patterns in one pass
Nested Subqueries Approach	O(n²)	O(n)	Use nested subqueries to calculate monthly income and check consecutive months separately
Window Functions with CTE	O(n log n)	O(n)	Use Common Table Expressions and window functions for efficient consecutive month detection

Single CTE with Lead/Lag (Optimal) — Algorithm Steps

Single CTE calculates monthly income and violation flags
Use window functions (LAG/LEAD) to check adjacent months
Apply consecutive violation logic in WHERE clause
Return distinct account IDs in one query

Visualization

Tap to expand

Step-by-Step Walkthrough

Unified CTE

Single query handles all calculations

Window Analysis

LAG/LEAD functions check adjacent months

Inline Filtering

WHERE clause applies consecutive logic

Direct Output

Returns results without intermediate tables

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass through data with sorting for window functions

⚡ Linearithmic

Space Complexity

O(k)

Only stores final result set, k = number of suspicious accounts

✓ Linear Space

Constraints

1 ≤ account_id ≤ 10⁵
1 ≤ max_income ≤ 10⁶
1 ≤ transaction_id ≤ 10⁶
1 ≤ amount ≤ 10⁵
type is either 'Creditor' or 'Debtor'
day is in format 'YYYY-MM-DD HH:MM:SS'

42.3K Views

High Frequency

~18 min Avg. Time

1.8K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Single CTE with Lead/Lag (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler