Bank Account Summary II - Solution & Explanation
Problem Statement
Table: Users
+--------------+---------+ | Column Name | Type | +--------------+---------+ | account | int | | name | varchar | +--------------+---------+ account is the primary key (column with unique values) for this table. Each row of this table contains the account number of each user in the bank. There will be no two users having the same name in the table.
Table: Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | trans_id | int | | account | int | | amount | int | | transacted_on | date | +---------------+---------+ trans_id is the primary key (column with unique values) for this table. Each row of this table contains all changes made to all accounts. amount is positive if the user received money and negative if they transferred money. All accounts start with a balance of 0.
Write a solution to report the name and balance of users with a balance higher than 10000. The balance of an account is equal to the sum of the amounts of all transactions involving that account.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Users table: +------------+--------------+ | account | name | +------------+--------------+ | 900001 | Alice | | 900002 | Bob | | 900003 | Charlie | +------------+--------------+ Transactions table: +------------+------------+------------+---------------+ | trans_id | account | amount | transacted_on | +------------+------------+------------+---------------+ | 1 | 900001 | 7000 | 2020-08-01 | | 2 | 900001 | 7000 | 2020-09-01 | | 3 | 900001 | -3000 | 2020-09-02 | | 4 | 900002 | 1000 | 2020-09-12 | | 5 | 900003 | 6000 | 2020-08-07 | | 6 | 900003 | 6000 | 2020-09-07 | | 7 | 900003 | -4000 | 2020-09-11 | +------------+------------+------------+---------------+ Output: +------------+------------+ | name | balance | +------------+------------+ | Alice | 11000 | +------------+------------+ Explanation: Alice's balance is (7000 + 7000 - 3000) = 11000. Bob's balance is 1000. Charlie's balance is (6000 + 6000 - 4000) = 8000.
Approach Overview
Problem Overview: You are given two database tables: Users and Transactions. Each transaction changes an account’s balance by a positive or negative amount. The task is to compute the final balance for every user and return the users whose total balance exceeds 10000.
Approach 1: SQL Join and Group By Approach (O(n) time, O(1) extra space)
This approach uses standard relational database operations. Join the Users table with the Transactions table on the account field so every transaction is associated with the correct user. Then aggregate the transaction values using SUM(amount) while grouping by the account (or user name). After computing the balance for each account, filter results using a HAVING SUM(amount) > 10000 clause. The key insight is that SQL aggregation already performs the full scan and accumulation efficiently inside the database engine. This solution relies heavily on SQL aggregation and database join operations.
Approach 2: Programmatically Process Data (O(n) time, O(n) space)
If the same problem is handled in an application layer instead of SQL, you simulate the aggregation manually. Iterate through all transactions and accumulate balances in a hash map keyed by account. Each iteration updates the stored sum: balance[account] += amount. After processing all transactions, iterate through the Users list and check whether the stored balance exceeds 10000. If it does, return the user name and balance. This approach uses a hash map to maintain constant‑time updates and lookups while aggregating transaction values.
Recommended for interviews: The SQL JOIN + GROUP BY approach is the expected answer for database-focused problems. It demonstrates you understand relational aggregation and filtering with HAVING. Implementing the hash-map aggregation programmatically still shows strong fundamentals because it mirrors what the database engine does internally—iterate through records, accumulate totals, then filter the result.
Approach 1: SQL Join and Group By Approach
This approach involves using SQL to join the `Users` and `Transactions` tables. A GROUP BY clause will be used to calculate the sum of transactions for each account. We will then filter users whose balances exceed 10000. This is a straightforward SQL-based solution that effectively uses database functionalities.
The code joins the `Users` table and `Transactions` table using the common `account` column. It then groups the data by `account` and `name` and calculates the total balance using the SUM function on the `amount` column. A HAVING clause filters results to include only users with a balance greater than 10000.
Code
SQL
Complexity
Time Complexity: O(N), where N is the number of transactions. Space Complexity: O(M), where M is the number of users with a balance greater than 10000.
Approach 2: Programmatically Process Data
This approach involves programmatically processing the data from tables using a HashMap or Dictionary. First, we iterate over the transactions table to compute the balances. Then, we filter out users whose balances exceed the required threshold. This uses basic data structures and is implemented in multiple programming languages.
This Python function expects a list of users and a list of transactions. It creates a dictionary mapping accounts to their balances by iterating through the transactions. It then checks each user, computes their balance, and adds those users who have a balance greater than 10000 to the result list. This is returned and can be printed or processed further.
Complexity
Time Complexity: O(N), where N is the number of transactions. Space Complexity: O(M), where M is the number of unique accounts.
Approach 3: Equi-Join + Group By + Sum
We can use an equi-join to join the Users table and the Transactions table on the condition of account, and then group by account to calculate the balance for each account using the SUM function. Finally, we can filter out the users whose balance is less than or equal to 10000.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| SQL Join and Group By Approach | Time Complexity: O(N), where N is the number of transactions. Space Complexity: O(M), where M is the number of users with a balance greater than 10000. |
| Programmatically Process Data | Time Complexity: O(N), where N is the number of transactions. Space Complexity: O(M), where M is the number of unique accounts. |
| Equi-Join + Group By + Sum | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Join and Group By | O(n) | O(1) extra | Best for database queries where aggregation and filtering should happen inside the SQL engine |
| Programmatic Aggregation with Hash Map | O(n) | O(n) | Useful when processing transaction data in application code instead of a relational database |
Video Solution
LeetCode 1587 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 5,848 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Bank Account Summary II easy or hard?
Bank Account Summary II Python/Java solution
How to solve Bank Account Summary II in O(n)?
What is the best approach for Bank Account Summary II?
Is Bank Account Summary II asked at Google/Amazon/Meta?
What data structure is used in Bank Account Summary II?
What is the time complexity of Bank Account Summary II?
Ready to solve this problem?
Practice Bank Account Summary II with our built-in code editor and test cases.
Practice on FleetCode