logo

Back to questions

Repeated Payments

Hard

Sometimes, payment transactions are repeated by accident; it could be due to user error, API failure or a retry error that causes a credit card to be charged twice.

Using the transactions table, identify any payments made at the same merchant with the same credit card for the same amount within 10 minutes of each other. Count such repeated payments.

Assumptions:

  • The first transaction of such payments should not be counted as a repeated payment. This means, if there are two transactions performed by a merchant with the same credit card and for the same amount within 10 minutes, there will only be 1 repeated payment.

Table:

Column NameType
transaction_idinteger
merchant_idinteger
credit_card_idinteger
amountinteger
transaction_timestampdatetime

Example Input:

transaction_idmerchant_idcredit_card_idamounttransaction_timestamp
1101110009/25/2022 12:00:00
2101110009/25/2022 12:08:00
3101110009/25/2022 12:28:00
4102230009/25/2022 12:00:00
6102240009/25/2022 14:00:00

Example Output:

payment_count
1

Explanation

Within 10 minutes after Transaction 1, Transaction 2 is conducted at Merchant 1 using the same credit card for the same amount. This is the only instance of repeated payment in the given sample data.

Since Transaction 3 is completed after Transactions 2 and 1, each of which occurs after 20 and 28 minutes, respectively hence it does not meet the repeated payments' conditions. Whereas, Transactions 4 and 6 have different amounts.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14