Question: Repeated Payments
"Identify all cases where the same user made two payments with the same amount and same merchant within 10 minutes of each other."

Step-by-Step Explanation:

You have a table called payments with:

• user_id
• payment_time (timestamp when the payment was made)
• merchant_id
• amount

You need to:

1. Find cases where a user made two payments:
   • Same amount
   • Same merchant_id
   • Within 10 minutes of each other.

Sample Data (payments):

Step 1: Compare Each Payment with the Previous Payment

For each user's payments, we need to look at the previous payment and check:

• If amount and merchant_id are the same.
• If payment_time is within 10 minutes of the previous payment.

We can use the LAG() function to get the previous payment details.

Step 2: Calculate Time Difference

We can use TIMESTAMPDIFF(MINUTE, previous_payment_time, current_payment_time) to find how many minutes passed between two payments.

If the time difference is ≤ 10 minutes, and other conditions match, it's a repeated payment.

SQL Query:

WITH payments_with_previous AS (
  SELECT
    user_id,
    payment_time,
    merchant_id,
    amount,
    LAG(payment_time) OVER (PARTITION BY user_id ORDER BY payment_time) AS previous_payment_time,
    LAG(merchant_id) OVER (PARTITION BY user_id ORDER BY payment_time) AS previous_merchant_id,
    LAG(amount) OVER (PARTITION BY user_id ORDER BY payment_time) AS previous_amount
  FROM
    payments
)

SELECT
  user_id,
  previous_payment_time AS first_payment_time,
  payment_time AS second_payment_time,
  merchant_id,
  amount
FROM
  payments_with_previous
WHERE
  previous_payment_time IS NOT NULL
  AND merchant_id = previous_merchant_id
  AND amount = previous_amount
  AND TIMESTAMPDIFF(MINUTE, previous_payment_time, payment_time) <= 10;

Explanation of SQL:

• LAG(payment_time), LAG(merchant_id), LAG(amount) get the previous payment time, merchant, and amount.
• WHERE conditions:
  • Check that the previous payment exists (not NULL).
  • Merchant and amount must match.
  • Time difference between the two payments must be 10 minutes or less.

Expected Output:

Explanation:

• User 1 made two payments of 100 at merchant 101 within 8 minutes.

Important Points:

• Always PARTITION BY user_id because you are comparing a user's payments to their own previous payments.
• Sort payments by payment_time inside OVER (ORDER BY ...).
• Only check payments that are directly consecutive (using LAG()).