It’s day 12 and my daily coding ritual is still going strong. I am feeling ecstatic today since I was able to solve a piece of work that involved using SQL which proves that my practice is yielding result. Today’s Leetcode challenge:

Given a table with Id no. and email address, I had to find out the email address that had duplicates.

Id	Email
1	a@email.com
2	c@email.com
3	a@email.com

Expected output”

Email
a@email.com

I used the Live SQL to test out my script first. First you must create a temporary table which I did by using ‘WITH’ clause.

 WITH
   Person AS (
     SELECT 1 AS Id, 'a@b.com' AS Email  FROM dual UNION ALL
     SELECT 2      , 'c@d.com'           FROM dual UNION ALL
     SELECT 3      , 'a@b.com'           FROM dual
 ) 

My solution

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

Breaking down the code

Query 1

 SELECT email, COUNT(email) FROM person GROUP BY email; 

Output

Email	COUNT(EMAIL)
a@email.com	2
c@email.com	1

Query 2

SELECT email, count(email) FROM person GROUP BY email HAVING COUNT(*) > 1; 

Output

Email	COUNT(EMAIL)
a@email.com	2

Other solution 1

Query 1

SELECT email FROM
(
SELECT email,row_number() OVER(PARTITION BY email ORDER BY ID) rn FROM person
)WHERE rn <> 1 

Breaking down the code

Query 1

SELECT id, email, row_number() OVER(PARTITION BY email ORDER BY ID) rn FROM person; 

Output

ID	Email	Rn
1	a@email.com	1
3	a@email.com	2
2	c@email.com	1

Query 2

SELECT * FROM
(
SELECT id, email, row_number() OVER(PARTITION BY email ORDER BY ID) rn FROM person
)
WHERE rn <> 1 

Output

Id	Email	Rn
3	a@email.com	2

Other solution 2

SELECT DISTINCT t1.email FROM person t1
WHERE EXISTS 
(SELECT * FROM person t2 
WHERE t2.email = t1.email 
AND t2.id != t1.id);

Breaking down the code

Query 1

SELECT * FROM person t1
WHERE EXISTS 
(SELECT * FROM person t2 
WHERE t2.email = t1.email 
AND t2.id != t1.id);

Output

Id	Email
1	a@email.com
3	a@email.com

Query 2

SELECT DISTINCT t1.email FROM person t1
WHERE EXISTS 
(SELECT * FROM person t2 
WHERE t2.email = t1.email 
AND t2.id != t1.id);

Output

Email
a@email.com

• Facebook
• Instagram
• LinkedIn
• GitHub