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.
- 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