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

Group 'success' payments that share a user, merchant and amount into clusters where every gap between consecutive payments is 10 minutes or less, then return the clusters holding at least two payments.

Return user_id, merchant_id, amount, cluster_start, cluster_end and payment_count, ordered by user, merchant, amount and start time.

A cluster can run far longer than ten minutes as long as no single gap does, so this is a chain rather than a fixed window. Failed payments are ignored, including as a link in the chain.

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:09:00
3           1        50           20.00   success  2026-05-01 10:18:00
4           1        50           20.00   success  2026-05-01 10:27:00
5           1        50           20.00   success  2026-05-01 10:47:00
6           2        50           30.00   success  2026-05-01 11:00:00
7           2        50           30.00   failed   2026-05-01 11:05:00
8           2        50           30.00   success  2026-05-01 11:20:00

Expected result

user_id  merchant_id  amount  cluster_start        cluster_end          payment_count
-------  -----------  ------  -------------------  -------------------  -------------
1        50           20.00   2026-05-01 10:00:00  2026-05-01 10:27:00  4