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 - Running Sum INPUT: Transactions Table acc_id day type amount 1 1 Deposit 100 1 2 Withdraw 30 1 3 Deposit 50 2 1 Deposit 200 2 2 Withdraw 80 Initial Balance = 0 Balance never goes negative ORDER BY: account_id, day ALGORITHM STEPS 1 Group by Account PARTITION BY account_id 2 Order by Day ORDER BY day ASC 3 Calc Signed Amount Deposit: +amt, Withdraw: -amt 4 Running Sum SUM() OVER window SUM(signed_amt) OVER ( PARTITION BY account_id ORDER BY day ROWS UNBOUNDED PRECEDING ) AS balance FINAL RESULT acc_id day balance 1 1 100 1 2 70 1 3 120 2 1 200 2 2 120 Balance Calculation: Account 1: Day 1: 0 + 100 = 100 Day 2: 100 - 30 = 70 Day 3: 70 + 50 = 120 Account 2: Day 1: 0 + 200 = 200 OK Key Insight: Window functions with SUM() OVER() enable efficient running balance calculation without self-joins. PARTITION BY resets the running sum for each account, while ORDER BY ensures chronological order. Use CASE WHEN to convert type to signed amount: Deposit = positive, Withdraw = negative. TutorialsPoint - Account Balance | Optimal Solution

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