Remove Duplicates Without Dropping | Dataford Interview Questions

Write a SQL query to identify and remove duplicate records from a staging table without dropping the table.

Treat rows as duplicates when source_system, subscriber_id, event_type, event_timestamp, and payload match, including matching NULL values. Retain the row with the smallest staging_id for each duplicate set.

Output

Return one row for every deleted record.
Include staging_id, source_system, subscriber_id, event_type, event_timestamp, and payload.
Order deleted records by staging_id ascending.

Schema

staging_events

Column	Type	Description
staging_idPK	INT	Unique identifier for the staging row
source_system	VARCHAR(50)	System that produced the record
subscriber_id	VARCHAR(30)	Subscriber identifier from the source system
event_type	VARCHAR(40)	Type of subscriber or network event
event_timestamp	TIMESTAMP	Timestamp supplied by the source system
payload	TEXT	Raw event payload

Your question is Remove Duplicates Without Dropping. Start with the requirements and the one table 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

Write a SQL query to identify and remove duplicate records from a staging table without dropping the table.

Output

Return one row for every deleted record.
Include staging_id, source_system, subscriber_id, event_type, event_timestamp, and payload.
Order deleted records by staging_id ascending.

Schema

staging_events

Column	Type	Description
staging_idPK	INT	Unique identifier for the staging row
source_system	VARCHAR(50)	System that produced the record
subscriber_id	VARCHAR(30)	Subscriber identifier from the source system
event_type	VARCHAR(40)	Type of subscriber or network event
event_timestamp	TIMESTAMP	Timestamp supplied by the source system
payload	TEXT	Raw event payload

Your question is Remove Duplicates Without Dropping. Start with the requirements and the one table 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.