Your question is SQL Deduplication Strategy. 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 SQL to find duplicate records and remove them while keeping the latest row per key.
Use the records table. Treat source_system and record_key together as the logical key. The latest row has the greatest updated_at; break timestamp ties by the greatest id.
id, source_system, record_key, updated_at, and payload.id ascending.| Column | Type | Description |
|---|---|---|
| idPK | INT | Unique row identifier |
| source_system | VARCHAR(50) | System that supplied the record |
| record_key | VARCHAR(100) | External logical key used for deduplication |
| updated_at | TIMESTAMP | Timestamp of the latest source update |
| payload | TEXT | Record content |