A fraud rule watches how fast a card is used. card_txns (txn_id, card_id, txn_at, amount) holds card transactions.
For each transaction, look at the same card's other transactions made in the hour up to it: from exactly 60 minutes before its txn_at (inclusive) to its own txn_at (inclusive). Another transaction at the very same moment counts, whichever txn_id it has. The transaction itself never counts.
- •
prior_hour_count — how many such transactions there are - •
prior_hour_amount — their total amount, 0 when there are none - •
is_velocity — TRUE when prior_hour_count is at least 3
Columns: txn_id, card_id, txn_at, amount, prior_hour_count, prior_hour_amount, is_velocity. Sort by card_id, txn_at, txn_id.