SQL for Duplicates and Trends | Dataford Interview Questions

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.

Output

One row per duplicate transaction group.
Columns: merchant_name, card_token, amount, transaction_date, transaction_type, duplicate_count, and first_transaction_id.
Include only groups with at least two transactions, ordered by merchant_name, transaction_date, amount, and card_token ascending.

Schema

visa_transactions

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

visa_merchants

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

Your question is SQL for Duplicates and Trends. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem