SQL Duplicate Consumer Profiles | Dataford Interview Questions

Write a SQL query to identify duplicate consumer profiles based on partial name and location matches.

Normalize names by ignoring case and non alphabetic characters. Profiles with NULL name components or a NULL location_id must be ignored.

Output

One row per matching profile pair, with profile_id_1, profile_id_2, profile_name_1, profile_name_2, city, and state.
Include each pair once, with the lower profile ID first.
Order by profile_id_1, then profile_id_2.

consumer_profiles

Column	Type	Description
profile_idPK	INTEGER	Unique consumer profile identifier
first_name	VARCHAR(100)	Consumer first name
last_name	VARCHAR(100)	Consumer last name
location_id	INTEGER	Reference to the consumer location

locations

Your question is SQL Duplicate Consumer Profiles. Start with the requirements and the two tables 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.

Write a SQL query to identify duplicate consumer profiles based on partial name and location matches.

Normalize names by ignoring case and non alphabetic characters. Profiles with NULL name components or a NULL location_id must be ignored.

One row per matching profile pair, with profile_id_1, profile_id_2, profile_name_1, profile_name_2, city, and state.
Include each pair once, with the lower profile ID first.
Order by profile_id_1, then profile_id_2.

consumer_profiles

Column	Type	Description
profile_idPK	INTEGER	Unique consumer profile identifier
first_name	VARCHAR(100)	Consumer first name
last_name	VARCHAR(100)	Consumer last name
location_id	INTEGER	Reference to the consumer location

locations

Your question is SQL Duplicate Consumer Profiles. Start with the requirements and the two tables 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.