Detect Duplicate Student Profiles

HardSQL · PostgreSQL

Problem

Write a SQL query to identify duplicate student profiles across multiple legacy databases based on partial matching criteria.

Use the three legacy student tables. Treat two profiles as duplicates when at least two of these non-null criteria match: normalized email, normalized phone digits, date of birth, or a name key consisting of the first initial and normalized last name. Do not match profiles from the same source database.

Output

One row per matching profile pair, with profile_id_a, source_a, profile_id_b, source_b, matching_criteria, and match_score.
Include only pairs with at least two matching criteria.
Sort by source_a, profile_id_a, source_b, and profile_id_b.

Schema

legacy_alpha_students

Column	Type	Description
legacy_idPK	INT	Profile identifier in the Alpha database
first_name	VARCHAR(100)	Student first name
last_name	VARCHAR(100)	Student last name
email	VARCHAR(255)	Student email address
phone	VARCHAR(30)	Student phone number
date_of_birth	DATE	Student date of birth

legacy_beta_students

Column	Type	Description
legacy_idPK	INT	Profile identifier in the Beta database
first_name	VARCHAR(100)	Student first name
last_name	VARCHAR(100)	Student last name
email	VARCHAR(255)	Student email address
phone	VARCHAR(30)	Student phone number
date_of_birth	DATE	Student date of birth

legacy_gamma_students

Column	Type	Description
legacy_idPK	INT	Profile identifier in the Gamma database
first_name	VARCHAR(100)	Student first name
last_name	VARCHAR(100)	Student last name
email	VARCHAR(255)	Student email address
phone	VARCHAR(30)	Student phone number
date_of_birth	DATE	Student date of birth

Tableslegacy_alpha_studentslegacy_beta_studentslegacy_gamma_students