Bank Account Summary II

Difficulty: Easy | Acceptance: 83.00% | Paid: No Topics: Database

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 and name of the user. Table: Transactions +--------------+---------+ | Column Name | Type | +--------------+---------+ | trans_id | int | | account | int | | day | date | | amount | int | | type | varchar | +--------------+---------+ trans_id is the primary key (column with unique values) for this table. Each row of this table contains information about the transaction. type is ENUM (‘Deposit’, ‘Withdrawal’). amount is the positive amount of money transferred.

Write a solution to report the name and the balance of the users who are in the US and have a positive net balance. The net balance is calculated as total deposits - total withdrawals. Return the result table in any order.

The result format is in the following example.

• Examples
• Constraints
• Hash Map Aggregation
• Brute Force

Examples

Example 1

Input: Users table: +------------+--------------+---------+ | account | name | country | +------------+--------------+---------+ | 900001 | Alice | US | | 900002 | Bob | UK | | 900003 | Charlie | US | +------------+--------------+---------+ Transactions table: +------------+------------+------------+----------+------------+ | trans_id | account | day | amount | type | +------------+------------+------------+----------+------------+ | 1 | 900001 | 2021-04-20 | 2000 | Deposit | | 2 | 900001 | 2021-04-20 | 1500 | Withdrawal | | 3 | 900002 | 2021-04-21 | 3000 | Deposit | | 4 | 900003 | 2021-04-22 | 1000 | Deposit | | 5 | 900003 | 2021-04-23 | 1500 | Withdrawal | +------------+------------+------------+----------+------------+ Output: +----------+----------+ | name | balance | +----------+----------+ | Alice | 500 | +----------+----------+ Explanation: Alice (US user) has a net balance of 2000 - 1500 = 500. Bob (UK user) should be excluded. Charlie (US user) has a net balance of 1000 - 1500 = -500, which is negative, so he should be excluded.

Constraints

Users table:
0 <= rows <= 1000
Transactions table:
0 <= rows <= 2000
amount: 0 <= amount <= 10^4
name length: 1 to 20

Hash Map Aggregation

Intuition We can simulate the database join and aggregation using a hash map. First, we identify all US users and initialize their balance to zero. Then, we iterate through transactions, updating the balance for valid accounts. Finally, we filter for positive balances.

Steps

• Create a hash map to store account info (name and balance).
• Iterate through the Users list. If the country is ‘US’, add the account to the map with balance 0.
• Iterate through the Transactions list. If the account exists in the map, add or subtract the amount based on the transaction type.
• Collect all entries from the map where the balance is greater than 0.
• Return the result as a list of lists.

def bankAccountSummary(users, transactions):
    balances = {}
    for acc, name, country in users:
        if country == 'US':
            balances[acc] = [name, 0]
    for _, acc, _, amount, t_type in transactions:
        if acc in balances:
            if t_type == 'Deposit':
                balances[acc][1] += amount
            else:
                balances[acc][1] -= amount
    result = [[name, bal] for acc, (name, bal) in balances.items() if bal > 0]
    return result

Complexity

• Time: O(N + M), where N is the number of users and M is the number of transactions.
• Space: O(N), to store the map of users.
• Notes: This approach is efficient as it processes each record exactly once.

Brute Force

Intuition For every transaction, we can scan the entire list of users to find the matching account and check their country. This avoids using extra space for a hash map but is slower.

Steps

• Initialize a list to store results.
• Iterate through the Transactions list.
• For each transaction, iterate through the Users list to find the matching account.
• If the user is from the US, calculate their net balance by summing all their transactions (this requires re-scanning or storing state).
• Since we need the final balance, we would typically need to aggregate first. A true brute force would aggregate by scanning all transactions for every user, leading to O(N*M).

def bankAccountSummary(users, transactions):
    # Create a lookup for US users first to simplify the brute force logic slightly
    # but strictly speaking, brute force implies nested loops without preprocessing.
    # Here we simulate the O(N*M) approach.
    us_users = {u[0]: u[1] for u in users if u[2] == 'US'}
    result = []
    
    # For every US user, calculate balance by scanning all transactions
    for acc, name in us_users.items():
        balance = 0
        for t in transactions:
            if int(t[1]) == int(acc):
                amount = int(t[3])
                if t[4] == 'Deposit':
                    balance += amount
                else:
                    balance -= amount
        if balance > 0:
            result.append([name, balance])
    return result

Complexity

• Time: O(N * M), where N is the number of users and M is the number of transactions. For each user, we scan all transactions.
• Space: O(N), to store the list of US users.
• Notes: This is significantly slower than the hash map approach when M is large.