Your question is SQL Window Deduplication. Start with the requirements and the two tables 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.
TransUnion's TruValidate identity-resolution pipeline can receive the same external record more than once. Write a PostgreSQL query that produces one retained record per business key for active customer accounts.
account_id, source_system, and external_record_id.ROW_NUMBER() to retain the most recent record by updated_at; break timestamp ties with the greatest update_id.COUNT() to show how many records existed in each duplicate group. Include groups with only one record.| Column | Type | Description |
|---|---|---|
| account_idPK | INTEGER | Unique customer account identifier |
| account_name | VARCHAR(100) | Customer or organization name |
| account_status | VARCHAR(20) | Current lifecycle status of the account |
| Column | Type | Description |
|---|---|---|
| update_idPK | INTEGER | Unique identity update identifier |
| account_id | INTEGER | Customer account associated with the update |
| source_system | VARCHAR(40) | System that submitted the record |
| external_record_id | VARCHAR(60) | Record identifier in the source system |
| updated_at | TIMESTAMP | Timestamp when the update was received |
| payload_hash | VARCHAR(64) | Hash identifying the submitted payload contents |