Your question is SQL Duplicate Record Cleanup. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |