Duplicate Emails - Practice Coding Problems

Duplicate Emails - Problem

Database Easy

Imagine you're working as a database administrator for a large company, and you need to identify duplicate email addresses in the employee database to clean up data inconsistencies.

You have a Person table with the following structure:

Column Name	Type
id	int
email	varchar

Where:

id is the primary key (unique identifier)
email contains the email addresses (all lowercase, never NULL)

Your task: Write a SQL query to find all email addresses that appear more than once in the table.

Example:

Input table:

id	email
1	a@b.com
2	c@d.com
3	a@b.com

Expected output:

Email
a@b.com

Input & Output

example_1.sql — Basic Duplicates

$ Input: Person table: | id | email | | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com |

› Output: | Email | | a@b.com |

💡 Note: a@b.com appears twice (id 1 and 3), so it's a duplicate. c@d.com appears only once, so it's not included.

example_2.sql — Multiple Duplicates

$ Input: Person table: | id | email | | 1 | x@y.com | | 2 | a@b.com | | 3 | x@y.com | | 4 | c@d.com | | 5 | a@b.com | | 6 | x@y.com |

💡 Note: x@y.com appears 3 times and a@b.com appears 2 times, both are duplicates. c@d.com appears only once.

example_3.sql — No Duplicates

$ Input: Person table: | id | email | | 1 | john@x.com| | 2 | jane@y.com| | 3 | bob@z.com |

› Output: (empty result)

💡 Note: Each email appears exactly once, so there are no duplicates to report.

Constraints

1 ≤ table size ≤ 1000
Email field is never NULL
All emails are in lowercase
id is the primary key (always unique)
Return result in any order

Visualization

Tap to expand

Understanding the Visualization

Collect all emails

Gather all email addresses from the Person table

Group identical emails

Use GROUP BY to automatically sort emails into groups

Count group sizes

COUNT(*) tells us how many emails in each group

Filter large groups

HAVING COUNT(*) > 1 keeps only groups with duplicates

Extract email addresses

SELECT email returns the duplicate email addresses

Key Takeaway

🎯 Key Insight: GROUP BY automatically handles the grouping and counting logic, while HAVING filters the results based on aggregate conditions - perfect for finding duplicates efficiently!

Asked in

A Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses GROUP BY email to group identical emails together, then applies HAVING COUNT(*) > 1 to filter groups containing duplicates. This approach runs in O(n) time with a single pass through the data, leveraging SQL's built-in aggregation capabilities for maximum efficiency.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with HAVING (Optimal)	O(n)	O(k)	Use GROUP BY to group emails and HAVING to filter duplicates
Brute Force (Nested Queries)	O(n²)	O(1)	Use nested SELECT to check each email against all others

GROUP BY with HAVING (Optimal) — Algorithm Steps

GROUP BY email to group identical emails together
Use COUNT(*) to count members in each group
Apply HAVING COUNT(*) > 1 to filter groups with duplicates
SELECT the email from qualifying groups

Visualization

Tap to expand

Step-by-Step Walkthrough

GROUP BY email

Group identical emails together

COUNT(*) for each group

Count members in each group

HAVING COUNT(*) > 1

Filter groups with more than 1 member

SELECT email

Return the email from qualifying groups

Code -

solution.c — C

// Optimal SQL query using GROUP BY and HAVING
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

const char* find_duplicate_emails_optimal() {
    const char* query = 
        "SELECT email as Email "
        "FROM Person "
        "GROUP BY email "
        "HAVING COUNT(*) > 1;";
    return query;
}

// C simulation would require more complex hash table implementation
// For brevity, showing the SQL approach which is the main solution
int main() {
    const char* sql_query = find_duplicate_emails_optimal();
    printf("Optimal SQL Query:\n%s\n", sql_query);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table to group and count emails

✓ Linear Growth

Space Complexity

O(k)

Space for k unique emails in the hash table for grouping

✓ Linear Space

42.0K Views

High Frequency

~15 min Avg. Time

1.9K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

GROUP BY with HAVING (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler