Delete Duplicate Emails - Practice Coding Problems

Delete Duplicate Emails - Problem

Database Easy

You are given a table Person that contains email addresses with their corresponding IDs.

Your task: Write a DELETE statement to remove all duplicate emails, keeping only the one with the smallest ID.

Important: You must write a DELETE statement, not a SELECT statement. The solution should modify the table in place.

Each email may appear multiple times with different IDs
Keep the row with the minimum ID for each email
Delete all other duplicate rows

Table Schema

Person

Column Name	Type	Description
`id` PK	int	Primary key, unique identifier
`email`	varchar	Email address (no uppercase letters)

Primary Key: id

Note: Each row represents a person with their email address

Input & Output

Example 1 — Basic Duplicate Removal

Input Table:

id	email
1	[email protected]
2	[email protected]
3	[email protected]

Output:

id	email
1	[email protected]
2	[email protected]

💡 Note:

The email [email protected] appears twice with IDs 1 and 3. We keep the row with the smaller ID (1) and delete the duplicate with ID 3.

Example 2 — Multiple Duplicates

Input Table:

id	email
1	[email protected]
2	[email protected]
3	[email protected]
4	[email protected]
5	[email protected]

Output:

id	email
1	[email protected]
2	[email protected]
4	[email protected]

💡 Note:

Multiple emails have duplicates: [email protected] (IDs 1,3) and [email protected] (IDs 2,5). We keep the rows with smaller IDs (1,2) and delete the duplicates (3,5).

Constraints

1 ≤ id ≤ 1000
email contains no uppercase letters
email follows valid email format

Visualization

Tap to expand

Understanding the Visualization

Identify

Find duplicate emails

Compare

Determine which IDs to keep

Delete

Remove duplicate rows

Key Takeaway

🎯 Key Insight: Use self-join or subquery to compare IDs and delete higher-numbered duplicates

Asked in

G Google 28 A Amazon 22 M Microsoft 18

Use DELETE with self-join or subquery with MIN(id) to remove duplicate emails while preserving the row with the smallest ID for each email address.

Table Schema

Person

Column Name	Type	Description
`id` PK	int	Primary key, unique identifier
`email`	varchar	Email address (no uppercase letters)

Primary Key: id

Note: Each row represents a person with their email address

Common Approaches

Approach	Time	Space	Notes
✓ Subquery DELETE	O(n log n)	O(n)	Delete rows not in the minimum ID subquery for each email
Self-Join DELETE	O(n²)	O(1)	Delete rows by joining table with itself to identify duplicates

Subquery DELETE — Algorithm Steps

Step 1: Subquery finds minimum ID for each email
Step 2: Delete all rows not matching the minimum ID condition

Visualization

Tap to expand

Step-by-Step Walkthrough

Group

GROUP BY email

Find Min

MIN(id) for each group

Delete

Remove rows not in min set

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Subquery with GROUP BY requires sorting

⚡ Linearithmic

Space Complexity

O(n)

Temporary space for subquery results

⚡ Linearithmic Space

125.0K Views

High Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Subquery DELETE — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler