Write a query to find duplicate records in a clinical transactions table and explain your strategy for removing them.
Treat records as duplicates when all business columns match: patient, transaction date, provider, transaction type, amount, and source system. Keep the record with the lowest transaction_id and identify the remaining records for removal.
transaction_id, patient_id, transaction_date, provider_id, transaction_type, amount, and duplicate_of_transaction_idtransaction_id ascending| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| patient_id | INT | Patient identifier |
| transaction_date | DATE | Date on which the transaction occurred |
| provider_id | INT | Healthcare provider identifier |
| transaction_type | VARCHAR(30) | Clinical transaction classification |
| amount | DECIMAL(10,2) | Transaction amount, including adjustments |
| source_system | VARCHAR(30) | System that supplied the transaction |