Email Duplicate is the question asked in many interviews to get the output we go through step-by-step discussion and understand the output.


Let's have a SQL Schema first containing some data:


CREATE TABLE If NOT EXISTS Person (id int, email varchar(255))

TRUNCATE TABLE Person

INSERT INTO Person (id, email) values ('1', 'a@b.com')

INSERT INTO Person (id, email) values ('2', 'c@d.com')

INSERT INTO Person (id, email) values ('3', 'a@b.com')


This above table will generate a table named Person which will look like this:

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


We require output:


Email
a@b.com



Now, we have the full detail of the question so, let's start with the query building:


SELECT email, count(email) FROM Person GROUP BY email;

This query will give us the email and count of emails grouped by email, so if the same email is more than one then the count of emails will be increased in counting. Let's see the output of the above query:



email count(email)
a@b.com 2
c@d.com 1


As you can see there is email a@b.com is 2 times in the table but we need to do more digging. We require only those email duplicates.

Extended query to find the duplicate email:

SELECT email, count(email) FROM Person GROUP BY email HAVING count(email)>1;


This query will provide the output as:


email count(email)
a@b.com 2

Now, Is this match our output?
Absolutely not.

We need to do one thing to make it perfect.


SELECT email FROM Person GROUP BY email HAVING count(email)>1;


The output of the above query:

email
a@b.com

Finally, we have the output.