SQL LeetCode: 182. Duplicate Emails

182. Duplicate Emails

Easy

SQL Schema

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

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

1. Using row_number() window function

SELECT
distinct Email
FROM (
SELECT
Email,
row_number() OVER (partition by Email) as rn_email
FROM
Person ) email_rn
WHERE rn_email > 1

2. Using Self join

SELECT
distinct p1.Email
FROM
Person p1
JOIN Person p2
ON p1.Email = p2.Email
and p1.Id <> p2.ID

3. Using group by

SELECT
distinct Email
FROM
Person
group by Email
having count(Email) > 1