Delete Duplicates, Keep One

An interviewer asks: the signups table has repeated emails. Delete the extra rows and keep one of each. The usual rule is to keep the oldest row of each email, the one with the smallest id. The idea in one sentence: list the ids to keep, then delete every row whose id is not in that list.

The check adds this signups table to shop.db. Ana signed up twice and Ben three times:

id | email | signed_up
1 | ana@example.com | 2026-01-10
2 | ben@example.com | 2026-02-03
3 | chloe@example.com | 2026-02-20
4 | ben@example.com | 2026-02-21
5 | ana@example.com | 2026-03-01
6 | dan@example.com | 2026-03-14
7 | ben@example.com | 2026-03-15
8 | eva@example.com | 2026-04-01

Delete by a list: WHERE id NOT IN (subquery) matches every row whose id the subquery doesn't list. For example, this removes the order items whose order no longer exists:

DELETE FROM order_items
WHERE order_id NOT IN (
    SELECT id
    FROM orders
)

Measure before and after: a DELETE with no WHERE removes every row, so count first. COUNT(*) counts the rows and COUNT(DISTINCT email) the different emails: when the two are equal, every email is there once. The check prints your check_query, runs your DELETE, prints check_query again, then the rows that are left:

signups | emails
8 | 5
(1 row)
signups | emails
5 | 5
(1 row)
id | email
1 | ana@example.com
2 | ben@example.com
3 | chloe@example.com
6 | dan@example.com
8 | eva@example.com
(5 rows)

Your task: write check_query, which counts the signups and the different emails in signups, and query, a DELETE that removes every row whose id is not the smallest id of its email.

Assignment
Follow the Coding Tutorial to write the two counts and the DELETE, one clause per line.

Hint
The ids to keep are the MIN(id) of each email, so the subquery needs GROUP BY email. Without it, MIN(id) is one id for the whole table and only one row is left.