Write a SQL query to identify duplicate records in a large dataset across multiple tables.
Use the supplied transaction, account, and channel data. Treat transactions with the same account, date, amount, merchant, and channel as duplicates. Exclude transactions whose account or channel cannot be matched.
account_id, transaction_date, amount, merchant_name, channel_name, duplicate_count, and transaction_idsaccount_id, transaction_date, amount, merchant_name, and channel_name| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Unique account identifier |
| account_reference | VARCHAR(20) | Masked account reference |
| customer_segment | VARCHAR(30) | Customer segment associated with the account |
| Column | Type | Description |
|---|---|---|
| channel_idPK | INT | Unique transaction channel identifier |
| channel_name | VARCHAR(30) | Channel through which the transaction was initiated |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| account_id | INT | Account associated with the transaction |
| channel_id | INT | Transaction channel identifier |
| transaction_date | DATE | Business date of the transaction |
| amount | NUMERIC(12,2) | Transaction amount |
| merchant_name | VARCHAR(80) | Merchant or transaction description |