SQL Fetch Duplicate Records | Dataford Interview Questions

Business Context

Hexaware Technologies uses RapidX workflows to support client onboarding and data quality checks. Duplicate contact records can cause repeated notifications, inaccurate reporting, and inconsistent ownership assignments.

Task

Write a PostgreSQL query to identify all contact records that belong to a duplicate email group. Treat email values as duplicates when they match after trimming whitespace and converting them to lowercase.

Requirements

Exclude contacts where email is NULL from duplicate detection.
Return every contact in a duplicate email group, the normalized email, and the number of records in that group.
Include the related business unit name when available, without removing contacts that have no business unit.
Order the result by normalized email and then by contact_id ascending.

Schema

contacts

Column	Type	Description
contact_idPK	INT	Unique contact identifier
full_name	VARCHAR(100)	Contact's full name
email	VARCHAR(255)	Contact email address
business_unit_id	INT	Associated business unit identifier
created_at	DATE	Date the contact was created

business_units

Column	Type	Description
business_unit_idPK	INT	Unique business unit identifier
unit_name	VARCHAR(100)	Business unit name

Your question is SQL Fetch Duplicate Records. 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