Your question is Duplicate Transactions Query. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Mastercard Decision Intelligence needs to flag potentially duplicated card authorizations before they are reviewed for fraud or processing errors. Write a PostgreSQL query that compares transactions against other transactions from the same card token, merchant, currency, and amount.
approved status, and exclude the original transaction from matching with itself.ROW_NUMBER() to rank duplicate candidates chronologically for each original transaction.| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Unique authorization identifier |
| card_token | VARCHAR(64) | Tokenized card identifier |
| merchant_id | VARCHAR(32) | Merchant identifier |
| amount | NUMERIC(12,2) | Authorization amount |
| currency | CHAR(3) | ISO currency code |
| transaction_ts | TIMESTAMPTZ | Authorization timestamp |
| status | VARCHAR(20) | Authorization status |