Code School of Analytics
ExpertWindow Functions50 points

The deepest balance drawdown

Nimbus Bank wants to find customers whose balance falls hard from a previous high: a risk signal for overdrafts and missed loan payments. For each account, take the balance after every transaction (opening_balance plus the cumulative signed amount_usd, ordered by txn_ts then id). The peak at any moment is the highest of the opening balance and all balances so far. The drawdown at that moment is peak minus current balance.

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: return the 5 accounts with the largest drawdown ever seen. Columns: customer name, account_id and max_drawdown_usd (the largest drawdown of that account, ROUND(x, 2)). Sort by max_drawdown_usd from highest to lowest, then by account_id.

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?

Two window steps: a running SUM for the balance, then a running MAX of that balance. In SQLite, MAX(a, b) with two arguments returns the larger value.