Write a SQL query to identify duplicate transactions or analyze merchant transaction volumes over time at Visa.
For this exercise, identify duplicate transaction groups where the merchant, card token, amount, transaction date, and transaction type are identical. Exclude records without a card token and exclude duplicate groups whose merchant is not present in the merchant reference table.
merchant_name, card_token, amount, transaction_date, transaction_type, duplicate_count, and first_transaction_id.merchant_name, transaction_date, amount, and card_token ascending.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| merchant_id | INT | Merchant receiving the transaction |
| card_token | VARCHAR(64) | Tokenized card identifier |
| amount | DECIMAL(12,2) | Transaction amount in the transaction currency |
| transaction_date | DATE | Calendar date of the transaction |
| transaction_type | VARCHAR(30) | Transaction operation type |
| Column | Type | Description |
|---|---|---|
| merchant_idPK | INT | Unique merchant identifier |
| merchant_name | VARCHAR(150) | Merchant display name |
| merchant_country | VARCHAR(2) | ISO-style merchant country code |