Code School of Analytics
IntermediateWindow Functions25 points

Rebuild the balance of account 15

A Nimbus Bank customer disputes her statement for account 15 and support needs the balance after every movement, in the exact order they happened. The balance on 2026-01-01 is accounts.opening_balance, and every transaction (card, salary or transfer) changes it by its signed amount_usd.

Dataset: Nimbus Bank. transactions.amount_usd is signed (money in is positive, money out is negative), already converted to USD. kind is CARD, SALARY, TRANSFER_IN or TRANSFER_OUT; merchant_id is only filled for card payments. accounts.opening_balance is the balance on 2026-01-01.

Task: for account 15 return id as txn_id, txn_ts, kind, amount_usd and balance_usd, the balance after that transaction (ROUND(x, 2)). Order the movements by txn_ts, then id, and keep that order in the result.

The data you can query

Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.

accounts36 rows
id INTEGERcustomer_id INTEGERaccount_type TEXTopened_on TEXTopening_balance REAL
11CURRENT2025-01-236938.69
22CURRENT2025-07-241839.27
33CURRENT2025-05-308009.17
44CURRENT2025-08-263864.19
55CURRENT2025-10-251836.52
66CURRENT2025-02-283834.62

Showing the first 6 of 36 rows.

customers30 rows
id INTEGERname TEXTcountry TEXTsegment TEXTjoined_on TEXT
1Amara OkaforUnited StatesRetail2025-01-18
2Liam CarterUnited KingdomRetail2025-07-23
3Priya NairGermanyStudent2025-05-30
4Hans BeckerIndiaStudent2025-08-23
5Sofia RossiPakistanRetail2025-10-24
6Mateo AlvarezBrazilRetail2025-02-24

Showing the first 6 of 30 rows.

loans14 rows
id INTEGERcustomer_id INTEGERprincipal_usd REALapr_pct REALissued_on TEXTstatus TEXT
121515018.982025-12-14CURRENT
2201245013.292025-07-02CURRENT
3301295022.932025-09-12CURRENT
422118507.012025-07-24DEFAULTED
5171075022.32025-01-28CURRENT
627150020.632026-02-06DEFAULTED

Showing the first 6 of 14 rows.

merchants18 rows
id INTEGERname TEXTcategory TEXTcountry TEXT
1Brightleaf GrocersGroceriesUnited States
2Nordmarkt FreshGroceriesGermany
3Casa Verde MercadoGroceriesMexico
4Saffron & SaltDiningUnited Kingdom
5Harbor Street RamenDiningSingapore
6Tandoor JunctionDiningIndia

Showing the first 6 of 18 rows.

transactions347 rows
id INTEGERaccount_id INTEGERmerchant_id INTEGERtxn_ts TEXTkind TEXTamount_usd REALis_flagged INTEGER
11142026-01-03 18:13:47CARD-374.10
21282026-01-04 22:16:50CARD-853.40
33NULL2026-01-06 15:14:07TRANSFER_IN1207.290
4582026-01-08 15:50:24CARD-820.290
525NULL2026-01-09 14:38:54TRANSFER_OUT-63.060
63012026-01-10 16:27:42CARD-72.240

Showing the first 6 of 347 rows.

Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.

Need a hint?

A running total is SUM(...) OVER (ORDER BY ...). Add the opening balance on top.