Two Swipes at the Same Merchant Inside a Day

To compare a row to another row of the SAME table, join the table to itself. Here: the same card, the same merchant, twice within 24 hours — the review queue a disputes team works through before anything is called a duplicate.

The join condition carries the whole question. b.account_id = a.account_id AND b.merchant_id = a.merchant_id pairs them up; b.txn_id > a.txn_id is the part people forget, and without it every transaction matches itself and every genuine pair comes back twice, once in each direction. txn_id increases with time in this ledger, so that same predicate also makes a the earlier swipe. The window is (julianday(b.txn_at) − julianday(a.txn_at)) * 24 <= 24.

Return account_id, merchant (name), first_txn_id, second_txn_id, first_txn_at, second_txn_at, first_amount, second_amount and hours_apart (rounded to 2 decimals). 18 pairs. Order by hours_apart ascending, then first_txn_id ascending, then second_txn_id ascending.

Be honest about what this finds: no pair in this ledger repeats the same amount, so these are repeat purchases, not proven duplicate charges. A rule like this produces a queue for a human, not a verdict.