Write a SQL query to find duplicate records in a transactions table and explain your approach to deduplication at New York Life.
For this exercise, treat records with the same policy, date, transaction type, amount, and source system as duplicates. Retain the earliest loaded record, using transaction_id as the tie-breaker.
transaction_id, loaded_at, duplicate_group_count, dedup_rank, and dedup_action.policy_id, transaction_date with nulls last, and transaction_id.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique identifier for the stored transaction record |
| policy_id | VARCHAR(20) | New York Life policy identifier |
| transaction_date | DATE | Business date of the transaction |
| transaction_type | VARCHAR(30) | Type of financial transaction |
| amount | DECIMAL(12,2) | Transaction amount |
| source_system | VARCHAR(30) | System that supplied the transaction |
| loaded_at | TIMESTAMP | Timestamp when the record was loaded |