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.
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 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
- 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.
- ROW_NUMBER() OVER (PARTITION BY customer_name, email, phone ORDER BY customer_id) numbers each group from 1. Wrap it in a subquery or CTE and keep rn = 1.
- Number the rows inside each group of identical customers, ordered by id, then keep the first of each group: SELECT customer_id, customer_name, email, phone, ROW_NUMBER() OVER (PARTITION BY customer_name, email, phone ORDER BY customer_id) AS rn FROM customers The row with rn = 1 in each group is the one with the smallest customer_id. A window function cannot be used in WHERE, so wrap this in a subquery or CTE and filter rn = 1 in the outer query. SELECT DISTINCT does not work here: it would collapse on all four columns, and customer_id is different in every row, so nothing would be removed.
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
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.