SQL Duplicate Detection at Scale | Dataford Interview Questions

Your question is SQL Duplicate Detection at Scale. 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.

Problem

SAS Customer Intelligence 360 receives event data from multiple ingestion channels. Write a PostgreSQL query that identifies repeated event records in the customer_events table, which may contain millions of rows.

Treat rows with the same customer_id, event_date, event_type, and event_amount as one business event. The record_id and ingested_at values identify individual ingested copies.

Requirements

Return every row belonging to a duplicate business-event group, including the first-ingested copy.
Show the number of records in each duplicate group and rank records by ingested_at, with record_id as a tie-breaker.
Label the earliest copy as original and later copies as duplicate.
Use a CTE and window functions. The query must retain groups where nullable business-key columns have matching NULL values.

Schema

customer_events

Column	Type	Description
record_idPK	BIGINT	Unique ingested record identifier
customer_id	VARCHAR(20)	SAS customer identifier
event_date	DATE	Date associated with the event
event_type	VARCHAR(40)	Event category
event_amount	NUMERIC(12,2)	Amount associated with the event
ingested_at	TIMESTAMP	Timestamp when the record entered the platform
source_system	VARCHAR(30)	Originating ingestion channel

Your question is SQL Duplicate Detection at Scale. 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.

Treat rows with the same customer_id, event_date, event_type, and event_amount as one business event. The record_id and ingested_at values identify individual ingested copies.

Requirements

Return every row belonging to a duplicate business-event group, including the first-ingested copy.
Show the number of records in each duplicate group and rank records by ingested_at, with record_id as a tie-breaker.
Label the earliest copy as original and later copies as duplicate.
Use a CTE and window functions. The query must retain groups where nullable business-key columns have matching NULL values.

Schema

customer_events

Column	Type	Description
record_idPK	BIGINT	Unique ingested record identifier
customer_id	VARCHAR(20)	SAS customer identifier
event_date	DATE	Date associated with the event
event_type	VARCHAR(40)	Event category
event_amount	NUMERIC(12,2)	Amount associated with the event
ingested_at	TIMESTAMP	Timestamp when the record entered the platform
source_system	VARCHAR(30)	Originating ingestion channel