Repeated Payments [Stripe SQL Interview Question]

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:

Example Input:

Example Output:

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!

Here are the steps to solve this question:

1. Bring together the transactions that are a part of the same group.
2. Work out the time difference between the groups' successive transactions.
3. Identify the identical transactions that took place within ten minutes of one another.

Step 1

For each transaction present in the table, we will obtain the time of the most recent identical transaction. With the , it is feasible. This function accesses the specified field’s values from the previous rows.

Run this query and we will explain more below.

Let's interpret the function:

• clause separates the result’s rows by the unique merchant ID, credit card and payment.
• Each partition is applied with a separate application of the function, and the computation is restarted for each partition (merchant ID, credit card and payment).
• Rows in each partition are sorted based on in the clause.

Showing 4 transactions worth $100 performed at Merchant ID 101 with credit card ID 1:

• Transaction ID 1 was the first of the series of payments, therefore it doesn't have a value from earlier transactions.
• Transaction ID 2 has a previous_transaction of 09/25/2022 12:00:00 which is pulled from the transaction in transaction ID 1.

Can you follow the pattern of the records in the ?

Step 2

Next, we should evaluate the difference in time between two consecutive identical transactions. We can simply subtract the values from the values as we now have the previous transaction time for each completed payment.

The following statement can be easily incorporated into the clause of the previous query.

The field makes it clear that there is an 8-minute lag between the first and second transactions. We will now convert this field into minutes format so that we can easily filter them.

One of the best methods to achieve that is to use the function.

• calculates the total number of seconds in a given interval.
• To calculate the difference in minutes, we divide these seconds by 60 (1 minute = 60 seconds). For example, the time interval for transaction id 5 is 1 hour and 9 minutes. calculates its value as 4140 seconds. By dividing it by 60, we arrive at 69 minutes.

This is how the outcome looks:

Step 3

The last thing we need to do is to gather all the identical transactions which occurred within a 10-minute window.

To do that, we must first convert the query into a common table expression (CTE). Then, we will filter the records using the clause for transactions a 10-minute or lesser window.

Additionally, the function allows us to count the filtered records.

Output for merchant ID 1:

Now, we will eliminate the unnecessary columns from the clause since they were only there for exploration purposes.

That's it! We have our final query :)