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: JOIN → GROUP BY → HAVINGInput TablesUsers900001 Alice900002 Bob900003 CharlieTransactions900001: +7000900001: -5000900003: +12000JOINProcessing Steps1. JOIN on account number2. GROUP BY user, SUM amounts3. HAVING balance > 10000Final ResultnamebalanceAlice11000Charlie12000Bob excluded: balance = 1000 ≤ 10000
Understanding the Visualization
1
Join Tables
Connect Users and Transactions on account
2
Group & Sum
Calculate balance per user
3
Filter
Keep only balances > 10000
Key Takeaway
🎯 Key Insight: Use HAVING with aggregate functions to filter grouped results

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