Find Duplicate Rows in MySQL | Dataford Interview Questions

Altimetrik's internal workforce platform must identify employee accounts that share the same email address. Write a PostgreSQL query that finds every duplicated non-null work_email and summarizes the affected accounts.

Requirements

Use a grouped subquery to identify email values appearing more than once.
Exclude NULL email values from duplicate detection.
Return each duplicated email, its account count, a comma-separated list of account IDs, and the number of accounts currently marked active.
Use a LEFT JOIN so account status information does not remove duplicate accounts without a status row. Sort by duplicate count descending, then email ascending.

Schema

employee_accounts

Column	Type	Description
account_idPK	INTEGER	Unique employee account identifier
work_email	VARCHAR(255)	Employee work email used for duplicate detection
created_at	DATE	Account creation date

employee_account_status

Column	Type	Description
account_idPK	INTEGER	Account identifier associated with a status
status	VARCHAR(20)	Current account status

Your question is Find Duplicate Rows in MySQL. 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