transactions(txn_id, user_id, amount, txn_at)

Flag the transactions worth more than three times that user's average transaction in the 30 days before it. The baseline covers [txn_at - 30 days, txn_at), so it excludes the transaction being judged, and a transaction is only flagged when the baseline holds at least five transactions.

Return txn_id, user_id, amount, prior_30d_avg rounded to two decimals and prior_count, ordered by txn_id.

Including the current transaction in its own baseline is the mistake that matters here: a large amount drags its own average up and hides the spike.

Tables

transactions

txn_id  user_id  amount  txn_at
------  -------  ------  -------------------
1       10       10.00   2026-03-01 10:00:00
2       10       10.00   2026-03-02 10:00:00
3       10       10.00   2026-03-03 10:00:00
4       10       10.00   2026-03-04 10:00:00
5       10       10.00   2026-03-05 10:00:00
6       10       100.00  2026-03-06 10:00:00
7       11       20.00   2026-01-01 10:00:00
8       11       20.00   2026-03-01 10:00:00
... 10 more row(s)

Expected result

txn_id  user_id  amount  prior_30d_avg  prior_count
------  -------  ------  -------------  -----------
6       10       100.00  10.00          5