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.

Output details

Sort source_a, profile_id_a, source_b, and profile_id_b ascending.
Orient each pair with the lexicographically smaller source in source_a, emitting each cross-source pair once.
Normalize email by trimming, lowercasing, removing whitespace, and treating an empty result as NULL; normalize phone by retaining digits and treating empty as NULL.
Construct name_key from the lowercased first alphanumeric character of first_name and lowercased alphanumeric last_name, treating an empty result as NULL.
List matching_criteria in the order email, phone, date_of_birth, name_key, separated by , with no trailing separator.

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