Delete duplicates, keep the lowest id

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Deduplication. About 15 minutes. Part of the Pro drill bank.

The contacts table has the same email address saved more than once, under different ids. You want a clean version with 1 row for each email, but you must not touch the original table. Do it in 3 steps. First, create a table called contacts_clean that has the same rows as contacts. Second, delete rows from contacts_clean so that, for every email, only the row with the lowest id is left. Third, finish with a SELECT that returns the rows that are left. Rows where email is NULL are special. A missing email is not a duplicate of another missing email, so all of those rows stay. The script may be run more than once, so it must give the same result every time.

Requirements

  • Create contacts_clean as a copy of contacts. Never change contacts itself.
  • For each non-NULL email, keep only the row with the lowest id.
  • Keep every row whose email is NULL.
  • End with a SELECT of id, email, name, ordered by id.
  • Running the whole script twice must not cause an error or a different answer.

Constraints

  • id is unique, so the lowest id is always one single row.
  • The statements run in order in one script: create, delete, select.
  • Use CREATE OR REPLACE TABLE for the copy so a second run starts fresh instead of failing because the table already exists.

Examples

Input: contacts id | email | name 1 | [email protected] | Ann 2 | [email protected] | Bob 3 | [email protected] | Ann B 4 | NULL | No Email 1 5 | NULL | No Email 2 6 | [email protected] | Bobby 7 | [email protected] | Cy 8 | [email protected] | Anne Output: id | email | name 1 | [email protected] | Ann 2 | [email protected] | Bob 4 | NULL | No Email 1 5 | NULL | No Email 2 7 | [email protected] | Cy Why this passes: [email protected] keeps id 1, [email protected] keeps id 2, [email protected] keeps id 7. The 2 rows with no email (4 and 5) both stay.

Input: contacts id | email | name 1 | [email protected] | Ann 2 | [email protected] | Ann B 3 | [email protected] | Bob Output: id | email | name 1 | [email protected] | Ann 3 | [email protected] | Bob Why this passes: One duplicated email: the higher id is deleted.

Topics: lakebench, sql, delete, duplicates, dml.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

Delete duplicates, keep the lowest id

Interview-style drill: Remove duplicate contacts from a working copy with a real DELETE and read back what remains.

`contacts` has duplicate emails. Do not change `contacts` itself. Work on a copy: 1. Create a table named `contacts_clean` with the same rows as `contacts`. 2. Delete rows from `contacts_clean` so that for every non-NULL `email` only the row with the lowest `id` remains. Rows with a NULL `email` are never duplicates of each other and all stay. 3. Finish with a SELECT of the remaining rows. Columns of the final SELECT: `id`, `email`, `name`. Order by `id`. The statements run in order in one script, and the script must be safe to run twice.