SQL Window Functions for Deduping | Dataford Interview Questions

Write a complex SQL query utilizing window functions to identify duplicate records and retrieve only the most recent entry per customer.

Use customer_records as the primary table. Treat customers with more than one record as duplicates, select the latest record by updated_at, and preserve records even when their source system is missing.

Output

One row per customer with duplicate records.
Return customer_id, record_id, full_name, email, phone, updated_at, and source_name.
Order by customer_id ascending, with record_id as the tie-breaker.

Schema

customer_records

Column	Type	Description
record_idPK	INT	Unique identifier for the customer record
customer_id	INT	Business identifier for the customer
full_name	VARCHAR(150)	Customer's full name
email	VARCHAR(255)	Customer email address
phone	VARCHAR(30)	Customer phone number
updated_at	TIMESTAMP	Timestamp when the record was last updated
source_id	INT	Source system that supplied the record

source_systems

Column	Type	Description
source_idPK	INT	Unique source system identifier
source_name	VARCHAR(100)	Human-readable source system name
source_code	VARCHAR(30)	Short source system code

Your question is SQL Window Functions for Deduping. 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