Objective

You are given a table of credit card transactions. Each row represents a single payment with details about the merchant, credit card, amount, and timestamp.

Table Schema:

The transactions table:

Task Requirements:

• Identify transactions that occur at the same merchant, with the same credit card and same amount, within 10 minutes of the previous matching transaction
• Count the number of such repeated payments, ignoring the first transaction in each sequence
• Use the LAG() window function partitioned by merchant, credit card, and amount

Output columns: payment_count

WITH prev_transactions AS (
    SELECT
        transaction_id,
        merchant_id,
        credit_card_id,
        amount,
        transaction_timestamp,
        LAG(transaction_timestamp) OVER (
            PARTITION BY merchant_id, credit_card_id, amount
            ORDER BY transaction_timestamp
        ) AS prev_timestamp
    FROM transactions
)
SELECT COUNT(*) AS payment_count
FROM prev_transactions
WHERE transaction_timestamp - prev_timestamp <= INTERVAL '10 minutes';

Explanation

Step 1: Retrieve the previous transaction timestamp

The CTE uses LAG() partitioned by merchant_id, credit_card_id, and amount to find the most recent previous transaction with the same merchant, card, and amount combination.

LAG(transaction_timestamp) OVER (
    PARTITION BY merchant_id, credit_card_id, amount
    ORDER BY transaction_timestamp
) AS prev_timestamp

Step 2: Filter for transactions within 10 minutes

The outer query filters for rows where the difference between the current and previous timestamp is 10 minutes or less, identifying the repeated payments.

WHERE transaction_timestamp - prev_timestamp <= INTERVAL '10 minutes'

Step 3: Count the repeated payments

COUNT(*) counts only the rows that pass the filter. The first transaction in each group naturally has a NULL previous timestamp and is excluded by the WHERE clause.