Flag and Remove Duplicate Customers | Dataford Interview Questions

NovaCart stores customer signups in a raw intake table and sometimes receives duplicate records for the same person. Write a SQL query to identify duplicates and mark which rows should be kept versus flagged for review.

Requirements

Treat records as duplicates when they have the same email_address and signup_date.
Keep the row with the earliest created_at; if there is a tie, keep the smallest customer_id.
Return only duplicate groups, including both the kept row and the rows that should be flagged.
Output the duplicate group key, row rank within the group, and a record_status column with values keep or flag_duplicate.

Schema

customer_signups

Column	Type	Description
customer_idPK	INT	Unique row identifier for each signup record
full_name	VARCHAR(100)	Customer name from the source system
email_address	VARCHAR(255)	Customer email used to detect duplicates
signup_date	DATE	Business signup date used in duplicate matching
source_system	VARCHAR(50)	Source that created the signup record
created_at	TIMESTAMP	Insertion timestamp used to decide which duplicate to keep
is_active	BOOLEAN	Whether the signup is currently active

Your question is Flag and Remove Duplicate Customers. 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

Requirements

Treat records as duplicates when they have the same email_address and signup_date.
Keep the row with the earliest created_at; if there is a tie, keep the smallest customer_id.
Return only duplicate groups, including both the kept row and the rows that should be flagged.
Output the duplicate group key, row rank within the group, and a record_status column with values keep or flag_duplicate.

Schema

customer_signups

Column	Type	Description
customer_idPK	INT	Unique row identifier for each signup record
full_name	VARCHAR(100)	Customer name from the source system
email_address	VARCHAR(255)	Customer email used to detect duplicates
signup_date	DATE	Business signup date used in duplicate matching
source_system	VARCHAR(50)	Source that created the signup record
created_at	TIMESTAMP	Insertion timestamp used to decide which duplicate to keep
is_active	BOOLEAN	Whether the signup is currently active

Your question is Flag and Remove Duplicate Customers. 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.