SQL Window Deduplication | Dataford Interview Questions

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.

Requirements

Partition records by account_id, source_system, and external_record_id.
Use ROW_NUMBER() to retain the most recent record by updated_at; break timestamp ties with the greatest update_id.
Use a windowed COUNT() to show how many records existed in each duplicate group. Include groups with only one record.
Return only records belonging to active accounts, and sort the output by account and business key.

Schema

customer_accounts

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

identity_updates

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

Your question is SQL Window Deduplication. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem