Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Delete duplicates, keep the lowest id

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

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.

Requirements

  • Use a real DELETE statement on the copy.
  • End with a SELECT of the remaining rows.

Constraints

  • Do not modify the contacts table.
  • id is unique.
  • The script may run more than once.

Examples

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

Input: contacts id | email | name 1 | a@x.com | Ann 2 | a@x.com | Ann B 3 | b@x.com | Bob Output: id | email | name 1 | a@x.com | Ann 3 | b@x.com | 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.