Write a SQL query to identify and eliminate duplicate records within a highly transactional database table.
Treat rows with the same account, event details, amount, reference, and source as duplicates. Retain the row with the lowest transaction_id and remove later copies. The query should return the records that it removed.
deleted_transaction_id, account_id, event_type, event_timestamp, amount, external_reference, and source_system| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction record identifier |
| account_id | INT | Customer account identifier |
| event_type | VARCHAR(30) | Transaction event classification |
| event_timestamp | TIMESTAMP | Timestamp when the transaction event occurred |
| amount | NUMERIC(12,2) | Transaction amount |
| external_reference | VARCHAR(60) | Reference supplied by the originating system |
| source_system | VARCHAR(40) | System that generated the transaction |