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.
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.
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.
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.
Java
Time Complexity: O(N), where N is the number of transactions. Space Complexity: O(M), where M is the number of unique accounts.
| 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. |
How many LeetCode problems should you solve? #leetcode #techinterview #developer #softwareengineer • CrioDo • 304,632 views views
Watch 9 more video solutions →Practice Bank Account Summary II with our built-in code editor and test cases.
Practice on FleetCode