Code School of Analytics
ExpertWindow Functions50 points

Three swipes in ten minutes

Card fraud often looks like a burst: a stolen card is used again and again within minutes. Nimbus Bank's rule is simple: a card payment is a burst hit when it is at least the third card payment of the same account inside a window of 10 minutes, in other words the card payment two payments before it (same account, card payments only, ordered by time and then id) happened 600 seconds or less earlier.

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: find the burst hits. For each account with at least one hit return account_id, burst_hits (number of hits), unflagged_hits (hits whose is_flagged is 0, the ones the fraud rules missed) and first_hit_ts (earliest hit timestamp). Sort by burst_hits 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?

LAG(value, 2) looks two rows back inside the partition. Convert timestamps to seconds with strftime('%s', ...) before subtracting.