LeetCode 182. Duplicate Emails (SQL Solution)

182. Duplicate Emails is a LeetCode easy question, but sometimes, when we encounter this type of question for the first time, it can be challenging to solve. In this post, I have provided solutions in three different ways, along with proper explanations. after reading this post you will learn a lot and become capable of solving similar questions.

The Duplicate Emails Question

Table: Person

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:

Output:

Explanation: a@b.com is repeated two times.

---

The Solutions

Solution 1 : Group by and Having Clause Approach

SELECT email FROM person GROUP BY email HAVING COUNT(email)>1

Explanation: first used group by on email column, then counted values within each group, and finally use the HAVING clause to filter out duplicate email addresses.

Solution 2 : self-join Approach

select distinct p1.email from person p1, person p2 where p1.id<>p2.id and p1.email=p2.email

Explanation: first performed self-join and filter record based on id should be different but email should be same.

Solution 3: nested query Approach

select Email from
(
  select Email, count(Email) as counted
  from Person
  group by Email
) as person11
where counted > 1;

Explanation: with the help of nested query first grouped the email column and counted each group, then with the help of outer query fiter the email column whose count is greater than 1 that how we got output.

Conclusion

I hope you understood the my explanation. don’t forget to visit this website for such question. do practice every day.