Account Balance - Problem

You are given a Transactions table with the following structure:

  • account_id (int): Account identifier
  • day (date): Transaction date
  • type (ENUM): Either 'Deposit' or 'Withdraw'
  • amount (int): Transaction amount

The combination of (account_id, day) forms the primary key.

Task: Write a SQL query to report the balance of each user after each transaction. Assume:

  • Initial balance for all accounts is 0
  • Balance will never go below 0
  • Return results ordered by account_id, then by day

Table Schema

Transactions
Column Name Type Description
account_id PK int Account identifier
day PK date Transaction date
type ENUM Transaction type: 'Deposit' or 'Withdraw'
amount int Transaction amount
Primary Key: (account_id, day)
Note: Each row represents one transaction. Balance starts at 0 for all accounts.

Input & Output

Example 1 — Basic Transactions
Input Table:
account_id day type amount
12321 2000-12-01 Deposit 1000
12321 2000-12-03 Withdraw 500
12321 2000-12-07 Deposit 300
54321 2000-12-01 Deposit 1500
54321 2000-12-02 Withdraw 200
Output:
account_id day type amount balance
12321 2000-12-01 Deposit 1000 1000
12321 2000-12-03 Withdraw 500 500
12321 2000-12-07 Deposit 300 800
54321 2000-12-01 Deposit 1500 1500
54321 2000-12-02 Withdraw 200 1300
💡 Note:

Account 12321: Starts with 1000 deposit (balance = 1000), then 500 withdrawal (balance = 500), then 300 deposit (balance = 800). Account 54321: Starts with 1500 deposit (balance = 1500), then 200 withdrawal (balance = 1300).
Example 2 — Single Account Multiple Days
Input Table:
account_id day type amount
1001 2023-01-01 Deposit 2000
1001 2023-01-02 Deposit 1000
1001 2023-01-03 Withdraw 500
Output:
account_id day type amount balance
1001 2023-01-01 Deposit 2000 2000
1001 2023-01-02 Deposit 1000 3000
1001 2023-01-03 Withdraw 500 2500
💡 Note:

Single account with consecutive transactions showing running balance: 2000 → 3000 → 2500 as deposits add to balance and withdrawals subtract from balance.

Constraints

  • 1 ≤ account_id ≤ 10^9
  • 1 ≤ amount ≤ 10^4
  • type is either 'Deposit' or 'Withdraw'
  • day is a valid date
  • Balance never goes below 0

Visualization

Tap to expand
Account Balance: Transaction ProcessingTransactionsaccounttypeamount12321Deposit100012321Withdraw50012321Deposit300RunningBalanceAccount Balanceaccounttypeamountbalance12321Deposit1000100012321Withdraw50050012321Deposit300800
Understanding the Visualization
1
Input
Transaction records with deposits and withdrawals
2
Window Function
Calculate running sum per account ordered by date
3
Output
Each transaction with its resulting balance
Key Takeaway
🎯 Key Insight: Use window functions to efficiently calculate running totals partitioned by account

Related Problems

Asked in
Amazon 15 Microsoft 12 JPMorgan 8
28.0K Views
Medium Frequency
~12 min Avg. Time
825 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