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

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

--

--

Pallavi Mirajkar Dantkale
Pallavi Mirajkar Dantkale

Written by Pallavi Mirajkar Dantkale

QA Engineer / Data Analyst — Highly committed to Quality Assurance and data analysis, advocate for quality and add the right value to the organization.

No responses yet

Write a response