SQL practice

SQL remove duplicates practice questions

6 problems, ordered from easiest. They run in your browser, and 2 are free.

Start with Distinct departments

The idea in a minute

Duplicates appear when the same record is loaded twice. The fix depends on what you want to keep: any one copy, the earliest, or the latest.

DISTINCT removes rows that are identical in every column. When rows differ in some column, such as the id, you number the rows inside each group with ROW_NUMBER and keep number 1.

Example: Keep the earliest event per user

CREATE TABLE events AS SELECT * FROM (VALUES (1,'u1','login'),(2,'u1','login'),(3,'u2','login'),(4,'u2','login'),(5,'u2','view')) t(event_id, user_id, type);
SELECT event_id, user_id, type FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, type ORDER BY event_id) AS rn
  FROM events
) WHERE rn = 1
ORDER BY event_id

What it prints

event_id  user_id  type
1         u1       login
3         u2       login
5         u2       view

What to watch for

  • Decide which copy to keep before you write code, then put that rule in the ORDER BY of ROW_NUMBER.
  • DISTINCT only works on whole rows. Two rows with different ids are not duplicates to it.
  • After removing duplicates, count the rows to check you kept the number you expected.

Deduplication problems

  1. Distinct departmentsbeginner · Free
  2. Deduplicate events with ROW_NUMBERintermediate · Free
  3. Duplicate customer-product pairsintermediate · Locked
  4. Keep lowest order_id per duplicate pairintermediate · Locked
  5. Delete duplicates, keep the lowest idintermediate · Locked
  6. Rows remaining after duplicate cleanupadvanced · Locked

Want a guided order across every topic? Open the practice sheets or the SQL hub.