Duplicate Emails

Difficulty: Easy | Acceptance: 73.70% | Paid: No Topics: Database

Table: Person +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id is the primary key column for this table. Each row of this table contains an email. The emails will not contain uppercase letters.

Write a SQL query to find all duplicate emails in a table named Person.

• Examples
• Constraints
• Group By and Having
• Self Join

Examples

Example 1:

Input:
Person table:
+----+---------+
| id | email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
Output:
+---------+
| Email   |
+---------+
| a@b.com |
+---------+
Explanation: a@b.com is repeated two times.

Constraints

It is guaranteed there are no duplicate rows in the Person table.

Group By and Having

Intuition We can group the records by the email column. If an email appears more than once, the count for that group will be greater than 1.

Steps

• Select the Email column.
• Group the results by Email.
• Filter the groups using the HAVING clause to keep only those with a count greater than 1.

class Solution:
    def duplicate_emails(self) -> str:
        # LeetCode 182 is a SQL problem.
        # The solution is the SQL query string.
        return "SELECT Email FROM Person GROUP BY Email HAVING COUNT(*) > 1"

Complexity

• Time: O(N) where N is the number of rows in the table (assuming indexing or efficient hashing).
• Space: O(N) to store the groups.
• Notes: This is the most idiomatic and readable SQL solution for this problem.

Self Join

Intuition We can join the table with itself on the condition that the emails are the same but the IDs are different. This effectively finds pairs of rows with the same email.

Steps

• Select the Email from the first instance of the table (p1).
• Join the Person table with itself (aliased as p1 and p2).
• Set the join condition to match emails (p1.Email = p2.Email) and ensure we are looking at different rows (p1.Id < p2.Id).
• Use DISTINCT to ensure each duplicate email is listed only once in the result.

class Solution:
    def duplicate_emails(self) -> str:
        # LeetCode 182 is a SQL problem.
        # The solution is the SQL query string.
        return "SELECT DISTINCT p1.Email FROM Person p1 JOIN Person p2 ON p1.Email = p2.Email WHERE p1.Id < p2.Id"

Complexity

• Time: O(N²) in the worst case without indexes, as it compares every row with every other row.
• Space: O(N) for the result set.
• Notes: Generally less efficient than GROUP BY for large datasets, but demonstrates a useful technique for comparing rows within the same table.