Watch 10 video solutions for Bank Account Summary II, a easy level problem involving Database. This walkthrough by Everyday Data Science has 5,595 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
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 | 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 |
