Account Running Balance Timeline

Medium

A banking ledger service stores one transaction per account per calendar day and needs to publish daily post-transaction balances. Table: Transactions account_id: integer account identifier day: transaction date (YYYY-MM-DD) type: transaction category ("Deposit" or "Withdraw") amount: positive integer amount Data guarantees: (account_id, day) is unique. Every account begins with balance 0 before its first row. Input is valid: when processing each account in day order, balance is never negative. Task: For each transaction row, compute the account balance immediately after applying that row. Treat Deposit as +amount and Withdraw as -amount. Output requirements: Return exactly three columns: account_id, day, balance Include every transaction row exactly once Sort by account_id ascending, then day ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Transactions:
| account_id | day        | type     | amount |
|------------|------------|----------|--------|
| 1          | 2021-11-07 | Deposit  | 2000   |
| 1          | 2021-11-09 | Withdraw | 1000   |
| 1          | 2021-11-11 | Deposit  | 3000   |
| 2          | 2021-12-07 | Deposit  | 7000   |
| 2          | 2021-12-12 | Withdraw | 7000   |

Output

[
  {"account_id":1,"day":"2021-11-07","balance":2000},
  {"account_id":1,"day":"2021-11-09","balance":1000},
  {"account_id":1,"day":"2021-11-11","balance":4000},
  {"account_id":2,"day":"2021-12-07","balance":7000},
  {"account_id":2,"day":"2021-12-12","balance":0}
]

Explanation

Each account keeps an independent running total. Account 1 evolves as 0->2000->1000->4000. Account 2 evolves as 0->7000->0.

Example 2

Input

Transactions:
| account_id | day        | type     | amount |
|------------|------------|----------|--------|
| 10         | 2022-01-01 | Deposit  | 500    |
| 10         | 2022-01-03 | Deposit  | 200    |
| 10         | 2022-01-04 | Withdraw | 300    |
| 20         | 2022-02-11 | Deposit  | 1000   |

Output

[
  {"account_id":10,"day":"2022-01-01","balance":500},
  {"account_id":10,"day":"2022-01-03","balance":700},
  {"account_id":10,"day":"2022-01-04","balance":400},
  {"account_id":20,"day":"2022-02-11","balance":1000}
]

Explanation

Balances are computed per account only; activity from one account never affects another account's timeline.

Example 3

Input

Transactions:
[]

Output

[]

Explanation

No rows means no account timeline rows to return.

Constraints

0 <= number of rows in Transactions <= 200000
1 <= account_id <= 1000000000
day is a valid date in YYYY-MM-DD format
(account_id, day) is unique
type is either Deposit or Withdraw
1 <= amount <= 1000000
For each account in ascending day order, running balance is never negative
Return exactly three columns: account_id, day, balance
Sort by account_id ascending, then day ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns account_id, day, balance

Transactions: | account_id | day | type | amount | |------------|------------|----------|--------| | 1 | 2021-11-07 | Deposit | 2000 | | 1 | 2021-11-09 | Withdraw | 1000 | | 1 | 2021-11-11 | Deposit | 3000 | | 2 | 2021-12-07 | Deposit | 7000 | | 2 | 2021-12-12 | Withdraw | 7000 |

[ {"account_id":1,"day":"2021-11-07","balance":2000}, {"account_id":1,"day":"2021-11-09","balance":1000}, {"account_id":1,"day":"2021-11-11","balance":4000}, {"account_id":2,"day":"2021-12-07","balance":7000}, {"account_id":2,"day":"2021-12-12","balance":0} ]

Transactions: | account_id | day | type | amount | |------------|------------|----------|--------| | 10 | 2022-01-01 | Deposit | 500 | | 10 | 2022-01-03 | Deposit | 200 | | 10 | 2022-01-04 | Withdraw | 300 | | 20 | 2022-02-11 | Deposit | 1000 |

[ {"account_id":10,"day":"2022-01-01","balance":500}, {"account_id":10,"day":"2022-01-03","balance":700}, {"account_id":10,"day":"2022-01-04","balance":400}, {"account_id":20,"day":"2022-02-11","balance":1000} ]