End of day balance of each account

Medium

Question

The accounts table has the opening_balance of each bank account (the money in it before the first transaction in the table). The transactions table has one row for every money movement. The kind is deposit (money in) or withdrawal (money out), and the amount is always a positive number.

For every account and every day on which the account had at least one transaction, return account_id, day (the txn_date) and closing_balance (the balance at the end of that day, after all transactions of that day and all earlier days). Days with no transaction do not need a row. Sort by account_id, then by day.

The order of the rows matters for this question.

window function
running total
sum over
case
cte
join
partition by

Read first: Lesson 47, SQL window functions: ranks, running totals, LAG and frames

Tables

2 tables

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

accounts3 columns · 3 rowsnot run yet

CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT NOT NULL, opening_balance INTEGER NOT NULL);

Example rows of the accounts table
id	owner	opening_balance
1	Kavita	5000
2	Lokesh	0
3	Meghna	12000

transactions5 columns · 8 rowsnot run yet

CREATE TABLE transactions (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL REFERENCES accounts(id), kind TEXT NOT NULL CHECK (kind IN ('deposit', 'withdrawal')), amount INTEGER NOT NULL, txn_date TEXT NOT NULL);

Example rows of the transactions table
id	account_id	kind	amount	txn_date
1	1	deposit	2000	2026-04-01
2	1	withdrawal	500	2026-04-01
3	1	withdrawal	3000	2026-04-03
4	1	deposit	1000	2026-04-04
5	2	deposit	800	2026-04-02
6	2	withdrawal	1000	2026-04-03
7	2	deposit	400	2026-04-03
8	3	withdrawal	12000	2026-04-05

About this SQL

A window function runs after GROUP BY, so group the transactions by account and day in a CTE first and run SUM() OVER on those daily rows. With ORDER BY and no frame, SUM() OVER adds up from the first row of the partition to the current row (and to every row with the same ORDER BY value). The balance can be negative; SQLite does not stop it.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready