payments(payment_id, user_id, merchant_id, amount, status, paid_at)

Find payments that look like an accidental double-tap: a 'success' payment with the same user, merchant and amount as the previous successful payment in that group, taken within 10 minutes of it.

Return payment_id, previous_payment_id and seconds_since_previous, ordered by payment_id. Order within a group by paid_at then payment_id.

Failed payments are ignored entirely, including as the "previous" payment, so a retry after a failure is not a duplicate.

In a run of three rapid payments, the second and third are both flagged, each against the one before it.

Tables

payments

payment_id  user_id  merchant_id  amount  status   paid_at
----------  -------  -----------  ------  -------  -------------------
1           1        50           20.00   success  2026-05-01 10:00:00
2           1        50           20.00   success  2026-05-01 10:01:00
3           1        50           20.00   success  2026-05-01 10:02:00
4           2        50           30.00   failed   2026-05-01 11:00:00
5           2        50           30.00   success  2026-05-01 11:01:00
6           3        50           40.00   success  2026-05-01 12:00:00
7           3        51           40.00   success  2026-05-01 12:01:00
8           4        50           60.00   success  2026-05-01 13:00:00
... 1 more row(s)

Expected result

payment_id  previous_payment_id  seconds_since_previous
----------  -------------------  ----------------------
2           1                    60
3           2                    60