This problem asks us to identify and report all email addresses that appear more than once in the Person table. This is a fundamental database query task often encountered in data cleaning, validation, or integrity checks where unique identifiers are expected.

The core challenge is to efficiently count the occurrences of each email and then filter for those counts exceeding one. We need a robust SQL approach that can handle potentially large datasets while maintaining good performance.

Intuition:

To find duplicate emails, we need to treat each distinct email address as a group and then check how many times each group appears. Imagine you have a pile of various colored balls, and you want to find out which colors appear more than once. You would first sort them by color, then count how many balls are in each color pile. Any color pile with more than one ball represents a duplicate color.

In SQL, the GROUP BY clause serves this purpose, allowing us to aggregate rows based on a common column (in this case, email). Once grouped, we can use an aggregate function like COUNT() to determine the number of rows within each group. The HAVING clause then acts as a filter on these aggregated groups, letting us specify conditions based on the aggregate results.

Step-by-step thinking:

Identify the relevant column: The problem is about finding duplicate emails, so the email column from the Person table is our focus.
Group by the column of interest: To count occurrences of each distinct email, we need to group the rows based on the email column. This is achieved using GROUP BY email. After this operation, all rows with the same email address are conceptually grouped together.
Count occurrences within each group: For each group created by the GROUP BY clause, we need to determine how many rows (or entries) belong to it. The aggregate function COUNT(email) will do precisely this, returning the number of times each unique email appears in the original table.
Filter for duplicates: Once we have the count for each email group, we only care about those emails that appear more than once. The HAVING clause is used to filter groups based on aggregate conditions. So, we'll apply HAVING COUNT(email) > 1 to keep only the groups where the email has duplicates.
Select the desired output: Finally, the problem asks us to report the duplicate emails themselves. Therefore, we select the email column from the filtered groups.

Time Complexity: O(N log N)
The dominant factor is typically the sorting required for the GROUP BY operation. In some database systems, if a hash-based aggregation is used, the average time complexity can be closer to O(N). N represents the total number of rows in the Person table.
Space Complexity: O(N)
This space is primarily used for storing the intermediate results of the GROUP BY operation (e.g., a hash table or sorted data structure to hold counts for each distinct email). In the worst-case scenario, all emails are distinct, requiring storage proportional to the number of rows.

Learn Complexity

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

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

307. Duplicate Emails

Example 1:

Constraints:

Learn

Topics

Hint