LeetCode 1587 - Bank Account Summary II

This problem asks us to analyze banking transaction data and determine which users currently have a balance greater than 10000. We are given two database tables: The Users table stores account information.

leetcodeeasydatabase

LeetCode Problem 1587

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to analyze banking transaction data and determine which users currently have a balance greater than 10000.

We are given two database tables:

The Users table stores account information. Each row contains:

  • account, the unique account number
  • name, the user's name

The Transactions table stores every transaction that affects an account. Each row contains:

  • trans_id, a unique transaction identifier
  • account, the account involved in the transaction
  • amount, the amount added or removed from the account
  • transacted_on, the date of the transaction

The key detail is how balances are calculated. Every account starts with a balance of 0, and the current balance is simply the sum of all transaction amounts for that account.

Positive amounts represent money received. Negative amounts represent money transferred out.

The goal is to return:

  • the user's name
  • the computed balance

but only for users whose balance is strictly greater than 10000.

The result can be returned in any order, which means we do not need an ORDER BY clause unless explicitly requested.

The problem is small in scope and categorized as an Easy SQL aggregation problem. The central operation is grouping transactions by account and summing their amounts.

Some important edge cases include:

  • Users with no transactions at all, their balance remains 0
  • Users whose balance is exactly 10000, they should not be included because the condition is strictly greater than 10000
  • Accounts with negative balances
  • Multiple transactions for the same account
  • Both positive and negative transaction values mixed together

The problem guarantees that:

  • account is unique in Users
  • trans_id is unique in Transactions
  • User names are unique
  • All accounts referenced are valid

These guarantees simplify the solution because we do not need to handle duplicate users or invalid foreign keys.

Approaches

Brute Force Approach

A brute force approach would process each user independently.

For every user in the Users table, we could scan the entire Transactions table and sum all transactions that belong to that account. After calculating the balance, we would check whether it exceeds 10000.

This approach is correct because it directly computes the balance definition given in the problem statement. However, it is inefficient because the transactions table is repeatedly scanned for every user.

If there are U users and T transactions, the total complexity becomes O(U × T).

Optimal Approach

The key observation is that balances are naturally computed using aggregation.

Instead of recalculating totals for every user separately, we can group all transactions by account once and compute the total sum for each account.

SQL provides this functionality directly through:

  • GROUP BY
  • SUM()
  • HAVING

After computing balances per account, we join the aggregated result with the Users table to retrieve user names.

This avoids repeated scans and computes each account balance exactly once.

Approach Time Complexity Space Complexity Notes
Brute Force O(U × T) O(1) Repeatedly scans all transactions for every user
Optimal O(T + U) O(U) Aggregates balances once using GROUP BY

Algorithm Walkthrough

  1. Start with the Transactions table because it contains the financial activity needed to compute balances.
  2. Group transactions by account using GROUP BY account. This ensures all transactions belonging to the same account are processed together.
  3. Compute the balance for each account using SUM(amount). Positive and negative values automatically combine correctly.
  4. Filter only accounts whose balance exceeds 10000 using the HAVING clause. HAVING is used instead of WHERE because filtering happens after aggregation.
  5. Join the aggregated balances with the Users table using the shared account column. This allows us to retrieve the corresponding user name.
  6. Select only the required output columns:
  • name
  • computed balance

Why it works

The algorithm works because every account balance is defined as the sum of all its transaction amounts. Grouping transactions by account guarantees that all relevant transactions are combined together exactly once. The aggregation step computes the correct balance, and the HAVING clause ensures only balances greater than 10000 are returned.

Python Solution

# Write your MySQL query statement below

SELECT
    u.name,
    SUM(t.amount) AS balance
FROM Users u
JOIN Transactions t
    ON u.account = t.account
GROUP BY u.account, u.name
HAVING SUM(t.amount) > 10000;

This solution begins by joining the Users and Transactions tables on the shared account field. The join ensures that each transaction is associated with the correct user.

Next, the query groups rows by account and user name. Since all transactions for an account are grouped together, SUM(t.amount) computes the full account balance.

The HAVING clause filters aggregated results. Only accounts with balances greater than 10000 remain in the final output.

The query returns exactly the two required columns: the user's name and their computed balance.

Go Solution

// There is no Go implementation for SQL database problems on LeetCode.
// The solution is written directly in SQL.

SELECT
    u.name,
    SUM(t.amount) AS balance
FROM Users u
JOIN Transactions t
    ON u.account = t.account
GROUP BY u.account, u.name
HAVING SUM(t.amount) > 10000;

For SQL problems on LeetCode, language specific implementations such as Go or Python are not applicable because the platform expects a database query instead of executable program code.

Worked Examples

Example 1

Users table:

account name
900001 Alice
900002 Bob
900003 Charlie

Transactions table:

trans_id account amount
1 900001 7000
2 900001 7000
3 900001 -3000
4 900002 1000
5 900003 6000
6 900003 6000
7 900003 -4000

Step 1: Group transactions by account

account transaction amounts
900001 7000, 7000, -3000
900002 1000
900003 6000, 6000, -4000

Step 2: Compute balances

account balance calculation balance
900001 7000 + 7000 - 3000 11000
900002 1000 1000
900003 6000 + 6000 - 4000 8000

Step 3: Apply HAVING balance > 10000

account balance qualifies
900001 11000 Yes
900002 1000 No
900003 8000 No

Step 4: Join with Users table

name balance
Alice 11000

This becomes the final result.

Complexity Analysis

Measure Complexity Explanation
Time O(T + U) Transactions are aggregated once and users are joined once
Space O(U) Aggregation stores balances per account

The dominant operation is the aggregation over the Transactions table. Each transaction contributes once to its account's total balance. The grouped results are then joined with the Users table. The database engine internally stores grouped account totals, which requires space proportional to the number of distinct accounts.

Test Cases

# Example case
# Alice has balance 11000 and should appear
"""
Users:
900001 Alice
900002 Bob
900003 Charlie

Transactions:
7000, 7000, -3000 for Alice
1000 for Bob
6000, 6000, -4000 for Charlie
"""

# Exact threshold case
# Balance exactly 10000 should NOT appear
"""
User balance = 10000
Expected output = empty
"""

# No transactions case
# User without transactions should not appear
"""
Users:
1 Alice

Transactions:
none

Expected output = empty
"""

# Negative balance case
# Negative balances should not appear
"""
Transactions:
-5000
-3000

Expected output = empty
"""

# Multiple qualifying users
# More than one user can appear
"""
Alice balance = 15000
Bob balance = 20000

Expected output:
Alice 15000
Bob 20000
"""

# Mixed positive and negative values
# Ensure subtraction works correctly
"""
Transactions:
20000
-5000
-3000

Final balance = 12000
Expected output = included
"""

# Large number of transactions
# Stress aggregation correctness
"""
1000 transactions of +20

Final balance = 20000
Expected output = included
"""
Test Why
Example input Validates the core aggregation logic
Balance exactly 10000 Ensures strict greater than comparison
No transactions Confirms accounts without activity are excluded
Negative balance Verifies negative totals are handled correctly
Multiple qualifying users Ensures query returns all valid accounts
Mixed positive and negative values Confirms additions and subtractions combine properly
Large transaction count Tests aggregation stability under many rows

Edge Cases

Users With No Transactions

A user may exist in the Users table without any matching rows in Transactions. Their balance should remain 0, which does not qualify for the result.

Because the query uses an inner JOIN, accounts without transactions are automatically excluded. This behavior is correct for the problem requirements.

Balance Exactly Equal to 10000

The condition states that balances must be higher than 10000, not greater than or equal to 10000.

Using:

HAVING SUM(t.amount) > 10000

correctly excludes accounts whose balance is exactly 10000.

Transactions With Negative Values

Some accounts may contain withdrawals or transfers represented by negative amounts. A buggy solution might accidentally ignore negative numbers or use absolute values.

Using SUM(amount) directly correctly incorporates both positive and negative transactions, producing the true final balance.

Multiple Transactions Per Account

Accounts can have many transaction rows. A naive implementation might accidentally return duplicate rows or compute partial balances.

Grouping by account ensures all transactions are aggregated into a single balance per user.