LeetCode #196 Delete Duplicate Emails - Solved in Oracle SQL

Correlated EXISTS Check for Earlier Matching Emails and ROW_NUMBER() Ranking with ROWID Delete Targeting

Image Source

LeetCode 196 asks us to remove duplicate email rows from the Person table while keeping the row with the smallest id for each repeated email. It is a short database problem, but it does a good job of testing how well you can translate a plain-language delete rule into SQL that removes only the rows that should go and leaves the right row behind.

From an Oracle SQL point of view, the best way to think about this problem is to stop viewing it as a delete first and start viewing it as a keep-one-row problem. For every email group, we want one survivor and we want that survivor to be the row with the lowest id. After that idea is firm, the delete becomes much easier to build. We can either check each row for an earlier matching row through a correlated subquery, or we can rank rows inside each email group with an analytic function and remove everything after the first position.

LeetCode #196 Delete Duplicate Emails

SQL Schema:

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (id, email) values ('1', 'john@example.com')
insert into Person (id, email) values ('2', 'bob@example.com')
insert into Person (id, email) values ('3', 'john@example.com')

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.

For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.

For Pandas users, please note that you are supposed to modify Person in place.

After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.

The result format is in the following example.

Example 1:

Input: 
Person table:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Output: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

Solution 1: Correlated EXISTS Check for Earlier Matching Emails

Reading the rule almost word for word fits Oracle SQL nicely. We delete a row only when the same email already appears on a row with a smaller id, which means the lowest id stays in place and every later match for that email is removed.