Write a SQL query to identify duplicate records in a high-volume transactional table and keep only the most recent entry.
For this exercise, records with the same account_id, transaction_reference, amount, and transaction_date are duplicates. Treat matching NULL values as part of the same duplicate key. Use processed_at as the recency field and transaction_id as the tie-breaker.
transaction_id, the duplicate key columns, processed_at, and duplicate_count.account_id, transaction_reference with NULLs last, processed_at descending, and transaction_id descending.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction record identifier |
| account_id | INT | Account associated with the transaction |
| transaction_reference | VARCHAR(40) | External transaction reference |
| amount | NUMERIC(12,2) | Transaction amount |
| transaction_date | DATE | Business date of the transaction |
| processed_at | TIMESTAMP | Timestamp when the record was processed |