De-duplicate the Pottery Mailing List

A pottery studio's newsletter sign-up form let people register more than once, so the same address now appears in several rows of mailing_list.

Write a statement that deletes the duplicates: for every email address, keep only the row with the smallest entry_id and delete every other row with that address. Two rows are duplicates when their email text is identical. Rows whose address appears only once must not be touched.

Do not return anything. The judge checks the remaining contents of mailing_list (entry_id and email) after your statement runs.

Tables

mailing_list — One row per sign-up form submission.

Example

mailing_list

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Changing Data · problem 59 of 70 · SQLite

The idea

Think of it as "keep a set, delete the rest". The rows to keep are one per address, the one with the smallest id: SELECT MIN(entry_id) FROM mailing_list GROUP BY email. Everything whose id is NOT IN that set is a later duplicate and goes. Addresses that appear once are their own minimum, so they are kept automatically.

The alternative phrases the same rule row by row: delete a row if another row with the same email and a smaller id exists. The correlated EXISTS sees the table as it was before the delete started, so the order in which SQLite removes rows does not change the result.

Before running any DELETE in real life, run the same condition as a SELECT first to see exactly which rows would disappear.

Solution

DELETE FROM mailing_list
WHERE entry_id NOT IN (
  SELECT MIN(entry_id)
  FROM mailing_list
  GROUP BY email
);

Another way

DELETE FROM mailing_list
WHERE EXISTS (
  SELECT 1
  FROM mailing_list AS earlier
  WHERE earlier.email = mailing_list.email
    AND earlier.entry_id < mailing_list.entry_id
);

Why common answers fail

Keeps the largest id for each address instead of the smallest.

DELETE FROM mailing_list WHERE entry_id NOT IN (SELECT MAX(entry_id) FROM mailing_list GROUP BY email);

Deletes every copy of a duplicated address, including the one that should be kept.

DELETE FROM mailing_list WHERE email IN (SELECT email FROM mailing_list GROUP BY email HAVING COUNT(*) > 1);

The subquery is not tied to the email, so it keeps only the single smallest id in the whole table.

DELETE FROM mailing_list WHERE entry_id > (SELECT MIN(entry_id) FROM mailing_list);

What the interviewer is checking

Tests whether you can express "keep one per group" as a set-based DELETE, and whether you reason about which copy survives.

Mistakes to avoid

• Deleting all copies of a duplicated address instead of all but one.
• Keeping the newest (MAX) row when the rule says smallest id.
• Using NOT IN with a subquery that can return NULL; here MIN over a NOT NULL key is safe, but in general prefer NOT EXISTS.

Follow-ups to expect

• Keep the earliest signed_up_on date instead (ROW_NUMBER() OVER (PARTITION BY email ORDER BY signed_up_on, entry_id)).
• Treat addresses as equal ignoring case (GROUP BY lower(email)).
• Prevent new duplicates (add a UNIQUE constraint on email and use INSERT ... ON CONFLICT DO NOTHING).

In real work

De-duplicating sign-ups, CRM contacts and event logs is a routine clean-up step before adding a unique constraint or syncing to another system.

Practice next