Peak Rolling 30-Day Spend Per Account

Credit-limit and velocity models both start here: the most a card spent in any 30-day window. For every transaction, sum the account's spend over the 30 days ending at that transaction, then take each account's maximum. The frame is the whole question: SUM(amount) OVER (PARTITION BY account_id ORDER BY julianday(txn_at) RANGE BETWEEN 30 PRECEDING AND CURRENT ROW) — ordering by julianday() turns the timestamp into a number of days, so a RANGE frame of 30 means 30 days, not 30 rows. ROWS BETWEEN 30 PRECEDING is the classic wrong answer here.

Compute the rolling sum in a CTE, then return account_id and peak_30d_spend (MAX of the rolling sum, rounded to 2 decimals) for the 20 accounts with the highest peak. Order by peak_30d_spend descending, then account_id ascending.