Suspicious Bank Accounts - Solution & Explanation
Problem Statement
Table: Accounts
+----------------+------+ | Column Name | Type | +----------------+------+ | account_id | int | | max_income | int | +----------------+------+ account_id is the column with unique values for this table. Each row contains information about the maximum monthly income for one bank account.
Table: Transactions
+----------------+----------+
| Column Name | Type |
+----------------+----------+
| transaction_id | int |
| account_id | int |
| type | ENUM |
| amount | int |
| day | datetime |
+----------------+----------+
transaction_id is the column with unique values for this table.
Each row contains information about one transaction.
type is ENUM (category) type of ('Creditor','Debtor') where 'Creditor' means the user deposited money into their account and 'Debtor' means the user withdrew money from their account.
amount is the amount of money deposited/withdrawn during the transaction.
A bank account is suspicious if the total income exceeds the max_income for this account for two or more consecutive months. The total income of an account in some month is the sum of all its deposits in that month (i.e., transactions of the type 'Creditor').
Write a solution to report the IDs of all suspicious bank accounts.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Accounts table: +------------+------------+ | account_id | max_income | +------------+------------+ | 3 | 21000 | | 4 | 10400 | +------------+------------+ Transactions table: +----------------+------------+----------+--------+---------------------+ | transaction_id | account_id | type | amount | day | +----------------+------------+----------+--------+---------------------+ | 2 | 3 | Creditor | 107100 | 2021-06-02 11:38:14 | | 4 | 4 | Creditor | 10400 | 2021-06-20 12:39:18 | | 11 | 4 | Debtor | 58800 | 2021-07-23 12:41:55 | | 1 | 4 | Creditor | 49300 | 2021-05-03 16:11:04 | | 15 | 3 | Debtor | 75500 | 2021-05-23 14:40:20 | | 10 | 3 | Creditor | 102100 | 2021-06-15 10:37:16 | | 14 | 4 | Creditor | 56300 | 2021-07-21 12:12:25 | | 19 | 4 | Debtor | 101100 | 2021-05-09 15:21:49 | | 8 | 3 | Creditor | 64900 | 2021-07-26 15:09:56 | | 7 | 3 | Creditor | 90900 | 2021-06-14 11:23:07 | +----------------+------------+----------+--------+---------------------+ Output: +------------+ | account_id | +------------+ | 3 | +------------+ Explanation: For account 3: - In 6-2021, the user had an income of 107100 + 102100 + 90900 = 300100. - In 7-2021, the user had an income of 64900. We can see that the income exceeded the max income of 21000 for two consecutive months, so we include 3 in the result table. For account 4: - In 5-2021, the user had an income of 49300. - In 6-2021, the user had an income of 10400. - In 7-2021, the user had an income of 56300. We can see that the income exceeded the max income in May and July, but not in June. Since the account did not exceed the max income for two consecutive months, we do not include it in the result table.
Approach Overview
Problem Overview: You are given a transaction table for bank accounts. An account becomes suspicious if the total Creditor transactions exceed 10000 in two consecutive months. The task is to return all such account_id values.
Approach 1: Monthly Aggregation + Self Join (O(n log n) time, O(n) space)
Start by aggregating transactions per account and per month. Use SUM(amount) and group by account_id and a month key such as DATE_FORMAT(day, '%Y-%m'). Filter groups where the total creditor amount exceeds 10000. Once you have qualifying months, perform a self join on the aggregated result where the same account_id appears in two rows whose months are consecutive. This approach works well because the expensive scan happens only once during aggregation, and the self join only runs on the reduced dataset.
This pattern is common in database problems: aggregate first, then compare adjacent records. The main insight is converting raw transactions into a monthly summary table, which makes consecutive-month detection trivial.
Approach 2: Window Function with LAG (O(n log n) time, O(n) space)
Another clean solution uses SQL window functions. After computing the monthly creditor totals, apply LAG(month) over a partition of account_id ordered by month. This gives access to the previous month directly. If the previous month plus one equals the current month and both totals exceed 10000, the account is suspicious. Window functions reduce the need for explicit joins and keep the logic easier to read.
This approach relies on features commonly used in SQL analytics queries. Window functions like LAG or LEAD are particularly useful when comparing adjacent rows inside the same group.
Approach 3: Consecutive Month Detection with CTE (O(n log n) time, O(n) space)
You can also structure the query with a Common Table Expression. First CTE: compute monthly creditor totals. Second CTE: filter rows where the total exceeds 10000. Finally, detect consecutive months by joining rows where the month difference equals one. This structure keeps each transformation step isolated and readable, which helps when queries grow more complex.
CTEs are frequently used in window function and analytical SQL workflows because they make multi-step transformations easier to reason about.
Recommended for interviews: The monthly aggregation plus self-join approach is the most expected solution. It shows you understand grouping, filtering, and relational comparisons in SQL. Window functions demonstrate deeper SQL knowledge, but the aggregation + join method communicates the core logic clearly and works across most SQL engines.
Solution
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Monthly Aggregation + Self Join | O(n log n) | O(n) | Most portable SQL solution; works across nearly all relational databases |
| Window Function with LAG | O(n log n) | O(n) | Cleaner logic when the database supports window functions |
| CTE + Consecutive Month Join | O(n log n) | O(n) | Useful when structuring multi-step SQL transformations for readability |
Video Solution
JP MORGAN LeetCode Medium “Suspicious Bank Accounts" 1843 Interview SQL Question Explanation | EDS • Everyday Data Science • 3,062 views views
Watch 3 more video solutions →Frequently Asked Questions
Is Suspicious Bank Accounts easy or hard?
Suspicious Bank Accounts Python/Java solution
How to solve Suspicious Bank Accounts in O(n)?
What is the best approach for Suspicious Bank Accounts?
Is Suspicious Bank Accounts asked at Google/Amazon/Meta?
What data structure is used in Suspicious Bank Accounts?
What is the time complexity of Suspicious Bank Accounts?
Ready to solve this problem?
Practice Suspicious Bank Accounts with our built-in code editor and test cases.
Practice on FleetCode