LeetCode SQL - DELETE

Window function

196. Delete Duplicate Emails

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP TABLE IF EXISTS Person;
CREATE TABLE
IF NOT EXISTS Person (Id INT, Email VARCHAR(30));
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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*Delete duplicate emails

- SOLUTION: window function
*/
WITH
cte AS (
SELECT
MIN(id) AS id
FROM
Person
GROUP BY
email
)
DELETE FROM Person
WHERE
id NOT IN (
SELECT
id
FROM
cte
);