Remove Duplicate Customer Records
SQL coding challenge · Difficulty: medium · Topic: Deduplication with ROW_NUMBER · +110 XP
Table: customers
+---------------+---------+
| Column | Type |
+---------------+---------+
| customer_id | INT | | customer_name | VARCHAR | | email | VARCHAR | | phone | VARCHAR |
+---------------+---------+
customer_id is the primary key.
Problem
-------
A data-ingestion fault inserted some customers more than once.
Two rows are duplicates only when ALL THREE of these are identical:
customer_name, email, phone
Keep exactly one row per customer: the one with the SMALLEST customer_id.
Return: customer_id, customer_name, email, phone
Order: customer_id ASC
Requirements
------------
1. RETURN the surviving rows with a SELECT. Do not write a DELETE - this is
graded on the rows your query outputs, and a DELETE outputs none.
2. All three columns form the key. Two people can share a name and an email
and still be different records if the phone differs.
3. Return every column: customer_id, customer_name, email, phone.
Example Input
-------------
+-------------+---------------+----------------+----------+
| customer_id | customer_name | email | phone |
+-------------+---------------+----------------+----------+
| 101 | Alice | alice@mail.com | 555-3311 | | 102 | Bob | bob@mail.com | 555-3322 | | 103 | Alice | alice@mail.com | 555-3311 | | 104 | Carol | carol@mail.com | 555-3333 | | 105 | Bob | bob@mail.com | 555-3322 | | 106 | Alice | alice@mail.com | 555-3311 | | 107 | Dan | dan@mail.com | 555-3344 | | 108 | Dan | dan@mail.com | 555-3399 |
+-------------+---------------+----------------+----------+
Expected Output
---------------
+-------------+---------------+----------------+----------+
| customer_id | customer_name | email | phone |
+-------------+---------------+----------------+----------+
| 101 | Alice | alice@mail.com | 555-3311 | | 102 | Bob | bob@mail.com | 555-3322 | | 104 | Carol | carol@mail.com | 555-3333 | | 107 | Dan | dan@mail.com | 555-3344 | | 108 | Dan | dan@mail.com | 555-3399 |
+-------------+---------------+----------------+----------+
Explanation
-----------
Alice appears three times - ids 101, 103 and 106 - with the same name, email and
phone. Keep id 101, the smallest. Drop 103 and 106.
Bob appears twice - ids 102 and 105. Keep id 102. Drop 105.
Carol appears once - id 104. Keep it.
Dan is the interesting one. Ids 107 and 108 share a name AND an email, but their
phones differ: 555-3344 against 555-3399. All three columns must match for a
row to be a duplicate, so these are two different records and BOTH are kept.
5 rows survive out of 8.
Production Constraints
- A row counts as a duplicate only when name, email, AND phone all match another row.
- Exactly 5 of the 8 source rows survive: the earliest-id record per true duplicate group, plus both of Dan's records, since his two phone numbers differ.
Why This Matters
Same lesson as its PySpark twin: deduplication has to be precise about what makes two rows "the same." Matching on a subset of fields, such as just name and email, would wrongly merge two different people who happen to share a name and email but have different phone numbers.
What this SQL challenge teaches you
“Remove Duplicate Customer Records” is a medium-level SQL challenge focused on Deduplication with ROW_NUMBER. Working through it gives you hands-on practice with scenario:data_quality, ROW_NUMBER, PARTITION BY, Deduplication, Window, Data Cleaning — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.
Concepts covered
- scenario:data_quality
- ROW_NUMBER
- PARTITION BY
- Deduplication
- Window
- Data Cleaning
How to approach it
If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:
- SELECT DISTINCT will not help: customer_id differs on every row, so nothing collapses.
- You need one survivor per group of rows that match on all of customer_name, email and phone: the one with the smallest customer_id. A window function can rank the rows inside each group in customer_id order, and the first-ranked row is the survivor. A window function's result cannot be filtered in the same query's WHERE, so think about where that filter has to go.
- Final hint: use ROW_NUMBER(), partitioned by the three columns that define a duplicate and ordered by customer_id, so the smallest id in each group is numbered 1. A window function cannot be filtered in the same query's WHERE, so compute it in a subquery or CTE and keep only the rows numbered 1 in the outer query, returning the four original columns in customer_id order.
How to practise it on PySpark.in
Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.
Related SQL challenges
- Deduplicate User Login Sessions
- Remove Duplicate Customer Records (PySpark)
- Top 3 Products per Category
- Running Total Revenue
- Find Duplicate Emails
- Median Salary per Department
Helpful resources
Frequently asked questions
Do I need to install Spark or a database to solve this?
No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.
Is this challenge free?
Yes - the problem, the sample dataset, the hints and unlimited test runs are free.
What level is it?
It is rated medium and covers Deduplication with ROW_NUMBER.