Bank Account Summary II - Problem

You are given two tables: Users and Transactions.

The Users table contains account information with columns:

  • account (int): Primary key, unique account number
  • name (varchar): User name (unique)

The Transactions table contains all account transactions with columns:

  • trans_id (int): Primary key, unique transaction ID
  • account (int): Account number
  • amount (int): Transaction amount (positive for deposits, negative for withdrawals)
  • transacted_on (date): Transaction date

Write a SQL query to find the name and balance of users whose balance is higher than 10000. The balance is calculated as the sum of all transaction amounts for each account. All accounts start with a balance of 0.

Return the result in any order.

Table Schema

Users
Column Name Type Description
account PK int Primary key, unique account number
name varchar User name (unique)
Primary Key: account
Transactions
Column Name Type Description
trans_id PK int Primary key, unique transaction ID
account int Account number (foreign key)
amount int Transaction amount (positive for deposits, negative for withdrawals)
transacted_on date Transaction date
Primary Key: trans_id

Input & Output

Example 1 — Basic Balance Calculation
Input Tables:
Users
account name
900001 Alice
900002 Bob
900003 Charlie
Transactions
trans_id account amount transacted_on
1 900001 7000 2020-08-01
2 900001 9000 2020-08-02
3 900001 -5000 2020-08-03
4 900002 1000 2020-09-01
5 900003 6000 2020-09-02
6 900003 6000 2020-09-03
Output:
name balance
Alice 11000
Charlie 12000
💡 Note:

Alice has transactions: 7000 + 9000 - 5000 = 11000 (> 10000, included). Bob has 1000 (≤ 10000, excluded). Charlie has 6000 + 6000 = 12000 (> 10000, included).
Example 2 — No High Balance Users
Input Tables:
Users
account name
900001 Alice
900002 Bob
Transactions
trans_id account amount transacted_on
1 900001 5000 2020-08-01
2 900002 3000 2020-08-02
Output:
name balance
💡 Note:

Alice has balance 5000 and Bob has balance 3000. Both are ≤ 10000, so no users are returned.
Example 3 — Multiple Transactions Per User
Input Tables:
Users
account name
900001 Alice
Transactions
trans_id account amount transacted_on
1 900001 15000 2020-08-01
2 900001 -2000 2020-08-02
3 900001 -1000 2020-08-03
Output:
name balance
Alice 12000
💡 Note:

Alice has multiple transactions: 15000 - 2000 - 1000 = 12000, which is greater than 10000, so she is included in the result.

Constraints

  • 1 ≤ Users.account ≤ 10^4
  • 1 ≤ Transactions.trans_id ≤ 10^5
  • 1 ≤ name.length ≤ 20
  • -10^4 ≤ amount ≤ 10^4
  • amount ≠ 0

Visualization

Tap to expand
Bank Account Summary II Find users with balance > 10000 using SQL JOIN INPUT TABLES Users Table account name 1 Alice 2 Bob 3 Charlie Transactions Table trans_id account amount 1 1 5000 2 1 8000 3 2 3000 4 3 15000 5 1 -2000 Join on: account ALGORITHM STEPS 1 JOIN Tables Link Users with Transactions Users JOIN Transactions 2 GROUP BY account Group all transactions GROUP BY U.account 3 SUM Amounts Calculate total balance SUM(T.amount) as balance 4 HAVING Filter Keep balance > 10000 HAVING SUM(amount)>10000 SQL Query: SELECT U.name, SUM(T.amount) FROM Users U JOIN Transactions T ON U.account = T.account GROUP BY U.account HAVING... FINAL RESULT Balance Calculations: Alice (account 1) 5000 + 8000 - 2000 = 11000 11000 > 10000 [OK] Bob (account 2) 3000 = 3000 3000 < 10000 [SKIP] Charlie (account 3) 15000 = 15000 15000 > 10000 [OK] Output Table: name balance Alice 11000 Charlie 15000 2 Users Found Key Insight: Use JOIN to combine Users and Transactions tables, then GROUP BY to aggregate all transactions per user. HAVING clause filters groups AFTER aggregation (unlike WHERE which filters rows BEFORE aggregation). Time: O(n) where n = number of transactions | Space: O(m) where m = number of users TutorialsPoint - Bank Account Summary II | Optimal Solution (SQL JOIN + GROUP BY + HAVING)

Related Problems

Asked in
Amazon 12 Microsoft 8 Apple 6
23.0K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen