Detect Duplicate Registrations | Dataford Interview Questions

Write a query to identify duplicate user registrations by analyzing timestamps and email variations at Credit Karma. Treat emails as equivalent when comparison is case-insensitive, surrounding whitespace is ignored, periods are removed from the local part, and plus-tags are removed. A registration is a duplicate when the previous equivalent registration occurred within 24 hours.

Output

One row per duplicate registration, including registration_id, user_id, email, registered_at, previous_registration_id, and hours_since_previous
Exclude malformed, blank, or NULL emails and registrations without a qualifying previous registration
Order by normalized email, registered_at, and registration_id

Schema

user_registrations

Column	Type	Description
registration_idPK	INT	Unique registration identifier
user_id	INT	Identifier assigned to the registered user
email	VARCHAR(255)	Email supplied during registration
registered_at	TIMESTAMPTZ	Timestamp when the registration was created

Your question is Detect Duplicate Registrations. 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

Output

One row per duplicate registration, including registration_id, user_id, email, registered_at, previous_registration_id, and hours_since_previous
Exclude malformed, blank, or NULL emails and registrations without a qualifying previous registration
Order by normalized email, registered_at, and registration_id

Schema

user_registrations

Column	Type	Description
registration_idPK	INT	Unique registration identifier
user_id	INT	Identifier assigned to the registered user
email	VARCHAR(255)	Email supplied during registration
registered_at	TIMESTAMPTZ	Timestamp when the registration was created

Your question is Detect Duplicate Registrations. 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.