Detect Duplicate Registrations

HardSQL · PostgreSQL

Problem

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

Output details

Round hours_since_previous to two decimal places.
Treat the 24-hour boundary as inclusive, allowing differences of exactly 24 hours.
Exclude registrations whose registered_at is NULL.
Classify emails as malformed when trimmed case-insensitive text is empty, has @ as its first character, or has @ as its last character.

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

Tablesuser_registrations

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

Output details

Round hours_since_previous to two decimal places.
Treat the 24-hour boundary as inclusive, allowing differences of exactly 24 hours.
Exclude registrations whose registered_at is NULL.
Classify emails as malformed when trimmed case-insensitive text is empty, has @ as its first character, or has @ as its last character.