Detect Duplicates in Database | Dataford Interview Questions

Your question is Detect Duplicates in Database. Start with the requirements and the two tables 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

FanDuel needs a data-quality query to identify player records that may represent the same person. Records should be considered potential duplicates when they share a non-empty normalized email address or a normalized phone number.

Write a PostgreSQL query that returns each duplicate player pair and the source systems associated with both records.

Requirements

Normalize emails by trimming whitespace and comparing them case-insensitively. Treat empty emails as missing.
Normalize phone numbers by removing non-numeric characters. Treat missing phones as unavailable match keys.
Return each unordered duplicate pair once, including the player IDs, names, matching reason, and source system names.
Include players without a source-system row, and order results by the first and second player IDs.

Schema

players

Column	Type	Description
player_idPK	INTEGER	Unique player record identifier
full_name	VARCHAR(100)	Player's full name
email	VARCHAR(255)	Registered email address
phone	VARCHAR(30)	Registered phone number, possibly formatted differently across sources

player_sources

Column	Type	Description
player_id	INTEGER	Player record identifier
source_name	VARCHAR(50)	System that created or supplied the player record