How do you handle duplicate records in a dataset using SQL? Write a query using window functions to identify and remove duplicates.
Use dataset_records and retain the earliest record for each duplicate set. The query should remove later duplicates and return the identifiers of the records removed.
removed_record_idremoved_record_id ascending| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique dataset record identifier |
| source_system | VARCHAR(40) | System that supplied the record |
| external_id | VARCHAR(40) | Identifier assigned by the source system |
| event_type | VARCHAR(30) | Type of dataset event |
| event_date | DATE | Business date of the event |
| amount | NUMERIC(10,2) | Amount associated with the event |
| loaded_at | TIMESTAMP | Timestamp when the record entered the dataset |