Identify Duplicate Records in SQL | Dataford Interview Questions

Bain & Company is validating client-contact intake records before loading them into its centralized analytics environment. Duplicate email addresses may represent repeated submissions and should be reviewed.

Write a PostgreSQL query to identify email addresses that occur more than once in the client_contacts table.

Requirements

Exclude rows where email is NULL, because a missing email cannot identify a duplicate contact.
Return each duplicated email and the number of records using it, ordered by duplicate count descending and email ascending.

Schema

client_contacts

Column	Type	Description
contact_idPK	INT	Unique contact-record identifier
contact_name	VARCHAR(100)	Contact's name
email	VARCHAR(255)	Contact email address used for duplicate detection
source_system	VARCHAR(50)	System that submitted the record

Your question is Identify Duplicate Records in SQL. Start with the requirements and the one table 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

Write a PostgreSQL query to identify email addresses that occur more than once in the client_contacts table.

Requirements

Exclude rows where email is NULL, because a missing email cannot identify a duplicate contact.
Return each duplicated email and the number of records using it, ordered by duplicate count descending and email ascending.

Schema

client_contacts

Column	Type	Description
contact_idPK	INT	Unique contact-record identifier
contact_name	VARCHAR(100)	Contact's name
email	VARCHAR(255)	Contact email address used for duplicate detection
source_system	VARCHAR(50)	System that submitted the record

Your question is Identify Duplicate Records in SQL. Start with the requirements and the one table 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.