Detect Duplicate Rows in SQL | Dataford Interview Questions

Provide a SQL query to find duplicates in a database table.

Assume a row is a duplicate when its email value appears more than once; NULL email values are not considered duplicates. Return every table row belonging to a duplicated email value.

Output

One row per duplicated user record
Columns: user_id, email, full_name, created_at, and duplicate_count
Include only email values appearing at least twice
Sort by email ascending, then user_id ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
full_name	VARCHAR(150)	User's full name
created_at	TIMESTAMPTZ	Timestamp when the user record was created

Your question is Detect Duplicate Rows 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