182. Duplicate Emails Solution - SQL Guide

The problem

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write a solution to report all the duplicate emails. Note that it's guaranteed that the email field is not NULL.

Return the result table in any order.

The result format is in the following example.

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.

Approach overview

Problem Overview: You are given a Person table containing email addresses. The goal is to return all email values that appear more than once. Each duplicate email should appear only once in the output.

This problem tests basic database querying skills. You need to identify repeated values in a column, which is a common pattern in database interviews and production analytics queries.

Approach 1: Using GROUP BY and HAVING Clause (O(n) time, O(k) space)

This is the most common SQL solution. You group rows by the email column and count how many times each email appears. The HAVING clause filters grouped results where the count is greater than 1. Internally the database scans the table and aggregates counts per unique email. This approach is concise, efficient, and exactly how SQL engines are designed to detect duplicates.

Typical query pattern:

SELECT email FROM Person GROUP BY email HAVING COUNT(email) > 1;

The time complexity is O(n) for scanning and grouping rows. Space complexity is O(k), where k is the number of distinct emails stored during aggregation.

Approach 2: Using Temporary Tables (O(n) time, O(k) space)

Another SQL approach stores aggregated counts in a temporary table. First, compute email frequencies using GROUP BY. Then query the temporary table to filter rows with counts greater than one. This technique is useful when intermediate results need to be reused in larger database pipelines or reporting queries.

Although the logic still relies on aggregation, separating it into steps can make complex queries easier to debug or extend. Performance characteristics remain roughly O(n) time with O(k) storage for unique emails.

Approach 3: SQL Query with Subquery to Find Duplicates (O(n) time, O(k) space)

A slightly different SQL pattern uses a subquery that calculates counts and then filters duplicates in the outer query. The inner query groups emails and counts occurrences, while the outer query selects only those above the threshold. This approach is logically identical to the HAVING solution but may appear in codebases that prefer subquery-style filtering.

Since the database still performs grouping and aggregation, the complexity remains O(n) time and O(k) memory.

Approach 4: HashMap or Dictionary Counting (O(n) time, O(k) space)

If the same problem is asked outside SQL, a hash table or dictionary works well. Iterate through all emails, increment a count in a map, and collect keys whose frequency exceeds one. Hash lookups and updates run in constant time on average, giving overall O(n) time complexity with O(k) extra space.

This approach appears frequently in coding interviews when the dataset is represented as arrays or objects instead of a relational table.

Recommended for interviews: Interviewers expect the GROUP BY + HAVING query for SQL versions of this problem. It demonstrates understanding of aggregation and filtering in relational databases. Mentioning the hash map counting strategy also shows you understand the underlying algorithm behind duplicate detection.

Approach 1: Using Group By and Having Clause

To identify duplicate emails, you can use the SQL GROUP BY clause, which groups rows that have the same values in specified columns into summary rows. Use the COUNT function to count the occurrences of each email, followed by the HAVING clause to filter the groups with more than one occurrence.

MySQL

SELECT email FROM Person GROUP BY email HAVING COUNT(email) > 1;

This SQL query selects the 'email' column from the Person table. The GROUP BY clause groups the rows by email, and HAVING COUNT(email) > 1 ensures that only groups with more than one occurrence are selected, effectively identifying duplicates.

Complexity

Time Complexity: O(N), where N is the number of rows in the table. The query needs to scan all rows to perform the grouping and counting.
Space Complexity: O(1), as it uses a fixed amount of space independent of the size of the input data, assuming a reasonable number of distinct emails.

Try this approach in the editor

Approach 2: Using Temporary Tables

Another approach to solve the problem of finding duplicate emails is by using a temporary table to store the counts of each email and then selecting from this table. This method might be helpful when performing complex operations in intermediate steps.

MySQL

CREATE TEMPORARY TABLE email_counts AS SELECT email, COUNT(*) as cnt FROM Person GROUP BY email; SELECT email FROM email_counts WHERE cnt > 1;

Firstly, create a temporary table, email_counts, to store each distinct email along with its count resulting from GROUP BY email. Then, select only those emails from this temporary table where the count, `cnt`, is greater than 1, indicating duplicates.

Complexity

Time Complexity: O(N) due to grouping and counting operations over the entire table.
Space Complexity: Additional O(M) space for the temporary table, where M is the number of distinct emails.

Try this approach in the editor

Approach 3: Using HashMap or Dictionary to Count Occurrences

This approach involves using a hash map or dictionary to count the occurrences of each email in the table. Once we have the counts, we can identify which emails appear more than once and output them.

Python

emails = ["a@b.com", "c@d.com", "a@b.com"]

email_count = {}
for email in emails:
    if email in email_count:
        email_count[email] += 1
    else:
        email_count[email] = 1

print("+---------+")
print("| Email   |")
print("+---------+")
for email, count in email_count.items():
    if count > 1:
        print(f"| {email} |")
print("+---------+")

Java

import java.util.HashMap;

public class DuplicateEmails {
    public static void main(String[] args) {
        String[] emails = {"a@b.com", "c@d.com", "a@b.com"};
        HashMap<String, Integer> emailCount = new HashMap<>();

        for (String email : emails) {
            emailCount.put(email, emailCount.getOrDefault(email, 0) + 1);
        }

        System.out.println("+---------+");
        System.out.println("| Email   |");
        System.out.println("+---------+");
        for (String email : emailCount.keySet()) {
            if (emailCount.get(email) > 1) {
                System.out.println("| " + email + " |");
            }
        }
        System.out.println("+---------+");
    }
}

C++

#include <iostream>
#include <unordered_map>
#include <vector>
using namespace std;

int main() {
    vector<string> emails = {"a@b.com", "c@d.com", "a@b.com"};
    unordered_map<string, int> emailCount;

    for (const auto& email : emails) {
        emailCount[email]++;
    }

    cout << "+---------+\n";
    cout << "| Email   |\n";
    cout << "+---------+\n";
    for (const auto& email : emailCount) {
        if (email.second > 1) {
            cout << "| " << email.first << " |\n";
        }
    }
    cout << "+---------+\n";

    return 0;
}

JavaScript

const emails = ["a@b.com", "c@d.com", "a@b.com"];
const emailCount = {};

emails.forEach(email => {
    if (emailCount[email]) {
        emailCount[email] += 1;
    } else {
        emailCount[email] = 1;
    }
});

console.log("+---------+");
console.log("| Email   |");
console.log("+---------+");
for (const email in emailCount) {
    if (emailCount[email] > 1) {
        console.log(`| ${email} |`);
    }
}
console.log("+---------+");

#include <stdio.h>
#include <string.h>
#define MAX_EMAILS 100
#define MAX_LENGTH 50

typedef struct {
    char email[MAX_LENGTH];
    int count;
} EmailCount;

int main() {
    char emails[MAX_EMAILS][MAX_LENGTH] = {"a@b.com", "c@d.com", "a@b.com"};
    int n = 3;
    EmailCount emailCounts[MAX_EMAILS] = {0};
    int uniqueEmails = 0;

    for (int i = 0; i < n; i++) {
        int found = 0;
        for (int j = 0; j < uniqueEmails; j++) {
            if (strcmp(emailCounts[j].email, emails[i]) == 0) {
                emailCounts[j].count++;
                found = 1;
                break;
            }
        }
        if (!found) {
            strcpy(emailCounts[uniqueEmails].email, emails[i]);
            emailCounts[uniqueEmails].count = 1;
            uniqueEmails++;
        }
    }

    printf("+---------+\n");
    printf("| Email   |\n");
    printf("+---------+\n");
    for (int i = 0; i < uniqueEmails; i++) {
        if (emailCounts[i].count > 1) {
            printf("| %s |\n", emailCounts[i].email);
        }
    }
    printf("+---------+\n");

    return 0;
}

using System;
using System.Collections.Generic;

public class Program {
    public static void Main() {
        string[] emails = {"a@b.com", "c@d.com", "a@b.com"};
        Dictionary<string, int> emailCount = new Dictionary<string, int>();

        foreach (string email in emails) {
            if (emailCount.ContainsKey(email)) {
                emailCount[email]++;
            } else {
                emailCount[email] = 1;
            }
        }

        Console.WriteLine("+---------+");
        Console.WriteLine("| Email   |");
        Console.WriteLine("+---------+");
        foreach (var email in emailCount) {
            if (email.Value > 1) {
                Console.WriteLine("| " + email.Key + " |");
            }
        }
        Console.WriteLine("+---------+");
    }
}

The Python solution involves using a dictionary to maintain a count of each email. We iterate over the emails, updating counts in the dictionary. Finally, we output emails whose count exceeds one.

Complexity

Time Complexity: O(n), where n is the number of emails to process the input.
Space Complexity: O(n), used by the dictionary to store email counts.

Try this approach in the editor

Approach 4: SQL Query to Find Duplicates

Here, we will utilize SQL to directly query the table and find duplicates by grouping the records based on the email and using a HAVING clause to filter out emails that appear more than once.

MySQL

SELECT email FROM Person GROUP BY email HAVING COUNT(id) > 1;

This SQL query groups the records in the Person table by email and then filters for groups having more than one record (i.e., duplicate emails) using the HAVING clause.

Complexity

Time Complexity: O(n log n), as it involves sorting or grouping.
Space Complexity: O(n), used by the GROUP BY for intermediate storage.

Try this approach in the editor

Complexity comparison

Approach	Complexity
Using Group By and Having Clause	Time Complexity: O(N), where N is the number of rows in the table. The query needs to scan all rows to perform the grouping and counting. Space Complexity: O(1), as it uses a fixed amount of space independent of the size of the input data, assuming a reasonable number of distinct emails.
Using Temporary Tables	Time Complexity: O(N) due to grouping and counting operations over the entire table. Space Complexity: Additional O(M) space for the temporary table, where M is the number of distinct emails.
Using HashMap or Dictionary to Count Occurrences	Time Complexity: O(n), where n is the number of emails to process the input. Space Complexity: O(n), used by the dictionary to store email counts.
SQL Query to Find Duplicates	Time Complexity: O(n log n), as it involves sorting or grouping. Space Complexity: O(n), used by the GROUP BY for intermediate storage.

Complexity analysis

Approach	Time	Space	When to Use
GROUP BY + HAVING	O(n)	O(k)	Standard SQL duplicate detection. Most common interview solution.
Temporary Table Aggregation	O(n)	O(k)	Useful when intermediate results are reused in complex queries.
Subquery with Aggregation	O(n)	O(k)	Alternative SQL pattern using nested queries.
HashMap / Dictionary Counting	O(n)	O(k)	When solving the problem outside SQL using arrays or objects.

Frequently asked questions

Is Duplicate Emails easy or hard?

Duplicate Emails is considered an Easy database problem. It focuses on basic SQL aggregation concepts such as GROUP BY and HAVING, which are fundamental skills for backend and data-related interviews.

Duplicate Emails Python/Java solution

In Python or Java, iterate through the list of emails and store frequencies in a dictionary or HashMap. After counting, iterate through the map and collect keys with counts greater than one. This approach runs in O(n) time with O(k) extra memory.

How to solve Duplicate Emails in O(n)?

Scan the dataset once and maintain counts per email. In SQL, use GROUP BY email and filter with HAVING COUNT(email) > 1. In programming languages like Python or Java, store counts in a hash map and return keys whose frequency exceeds one.

What is the best approach for Duplicate Emails?

The standard solution uses SQL GROUP BY with a HAVING clause. Group rows by the email column and filter groups where COUNT(email) > 1. This performs a single table scan with aggregation and runs in roughly O(n) time depending on the database engine.

Is Duplicate Emails asked at Google/Amazon/Meta?

Duplicate detection problems are common in database and backend interviews at companies like Google, Amazon, and Meta. While the exact question may vary, candidates are often expected to write SQL queries using GROUP BY, HAVING, or window functions.

What data structure is used in Duplicate Emails?

The conceptual data structure behind the problem is a hash table that tracks frequency counts. SQL engines implement a similar idea internally during aggregation, while coding solutions explicitly use HashMap or dictionary structures.

What is the time complexity of Duplicate Emails?

Most SQL solutions run in O(n) time because the database scans the table once and aggregates counts for each unique email. Space complexity is O(k), where k is the number of distinct email values stored during grouping.

Duplicate Emails solution and explanation

The problem

Approach overview

Approach 1: Using Group By and Having Clause

Approach 2: Using Temporary Tables

Approach 3: Using HashMap or Dictionary to Count Occurrences

Approach 4: SQL Query to Find Duplicates

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Duplicate Emails solution and explanation

The problem

Approach overview

Approach 1: Using Group By and Having Clause

Approach 2: Using Temporary Tables

Approach 3: Using HashMap or Dictionary to Count Occurrences

Approach 4: SQL Query to Find Duplicates

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems