Write a SQL query to find duplicate records in a transaction table and explain how you would resolve them at Zions Bancorporation.
Treat transactions with the same account, date, amount, type, and reference number as duplicates. Return only the records that should be reviewed for quarantine or removal, retaining the lowest transaction ID as the canonical record.
transaction_id, account_id, transaction_date, amount, transaction_type, reference_number, duplicate_count, and duplicate_rankaccount_id, transaction_date, and transaction_id| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Unique transaction identifier |
| account_id | VARCHAR(20) | Bank account identifier |
| transaction_date | DATE | Business date of the transaction |
| amount | NUMERIC(12,2) | Transaction amount |
| transaction_type | VARCHAR(20) | Transaction classification |
| reference_number | VARCHAR(30) | External transaction reference |
| status | VARCHAR(20) | Processing status |