SQL Duplicate Accounts with Windows | Dataford Interview Questions

AllianceBernstein's client data is consolidated from multiple customer systems, so the same investor may appear under different capitalization, spacing, or phone formatting. Write a PostgreSQL query to identify active investment accounts belonging to duplicate customer profiles.

Requirements

Join customer profiles to investment accounts.
Normalize email addresses and phone numbers before comparing profiles.
Use window functions to return every active account in a duplicate group, including the duplicate count and a deterministic rank.
Exclude records with missing matching fields, inactive accounts, and profiles that do not belong to a duplicate group.

Schema

customer_profiles

Column	Type	Description
profile_idPK	INT	Customer profile identifier
legal_name	VARCHAR(255)	Customer's legal name
email	VARCHAR(255)	Customer email address
phone	VARCHAR(40)	Customer phone number
source_system	VARCHAR(50)	Originating customer system

investment_accounts

Column	Type	Description
account_idPK	INT	Investment account identifier
profile_id	INT	Related customer profile
account_number	VARCHAR(30)	Account number
account_type	VARCHAR(30)	Account classification
status	VARCHAR(20)	Account status
opened_on	DATE	Account opening date

Your question is SQL Duplicate Accounts with Windows. 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.

Problem