Finding Duplicate SQL Records | Dataford Interview Questions

EAB Navigate stores advising interactions submitted by advisors. Data quality analysts need to identify repeated interaction records before these records are used in advising activity reports.

Write a PostgreSQL query that finds duplicate rows in advising_interactions. Two interactions are duplicates when they have the same student_id, advisor_id, interaction_date, channel, and topic. The interaction_id is a unique row identifier and must not be part of the duplicate definition.

Requirements

Use a CTE to identify duplicate combinations with COUNT(*) > 1.
Return every source row belonging to a duplicate group, including its interaction_id and duplicate count.
Join to students to display the student name. Preserve duplicate interactions even when no student record exists.
Treat duplicate rows with a NULL topic as belonging to the same duplicate group, and order results by student ID, interaction date, and interaction ID.

Schema

advising_interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction row identifier
student_id	INT	EAB student identifier
advisor_id	INT	Advisor identifier
interaction_date	DATE	Date of the advising interaction
channel	VARCHAR(30)	Interaction channel
topic	VARCHAR(100)	Advising topic

students

Column	Type	Description
student_idPK	INT	Student identifier
first_name	VARCHAR(50)	Student first name
last_name	VARCHAR(50)	Student last name

Your question is Finding Duplicate SQL 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