Find Duplicate Names | Dataford Interview Questions

Your question is Find Duplicate Names. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Epsilon's employee directory contains a non-unique name column. Write a PostgreSQL query that identifies names appearing more than once and summarizes the matching employees by department.

Requirements

Exclude rows where name is NULL, because they do not represent a usable employee name.
Return each duplicated name and the total number of employees using that name.
Include a comma-separated, alphabetically ordered list of associated department names. Preserve duplicate-name employees whose department is not assigned.
Sort the final result alphabetically by employee name.

Schema

emp

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
name	VARCHAR(100)	Employee display name, which may be duplicated
department_id	INTEGER	Assigned department identifier

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	Epsilon department name