DA0-002 Data Acquisition and Preparation Practice Question
A data analyst needs to identify duplicate customer records based on email and phone number. Which SQL techniques can be used to find duplicates? (Select TWO).
⚠ Common exam trap
DA0-002 often tests SQL logical processing order, tricking candidates into selecting a query that references a window-function alias in WHERE, which is invalid because window functions are evaluated after WHERE.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use a CTE to assign ROW_NUMBER() and then select rows where rn > 1
Option E is correct because grouping by email and phone with GROUP BY and filtering with HAVING COUNT(*) > 1 returns exactly those email/phone combinations that appear more than once, which is the standard way to detect duplicate records. Option D is correct because a CTE can compute ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) and then the outer query filters WHERE rn > 1, reliably identifying all rows beyond the first occurrence of each duplicate key. Option A is not correct because ORDER BY only sorts rows and does not detect or filter duplicates. Option B is not correct because SELECT DISTINCT removes duplicates and returns only unique combinations, the opposite of finding them. Option C is not correct because a window function cannot be referenced in the WHERE clause of the same query level, so WHERE rn > 1 would fail; the ROW_NUMBER() must be wrapped in a subquery or CTE as in option D.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SELECT email, phone FROM customers ORDER BY email, phone
Why it's wrong here
ORDER BY merely sorts rows; it neither groups nor counts repeated email and phone combinations, so duplicates remain indistinguishable. It tempts analysts inspecting data visually, which suits small ad-hoc review, whereas duplicate detection needs aggregation such as GROUP BY with HAVING COUNT(*) > 1 to quantify repetitions.
- ✗
SELECT DISTINCT email, phone FROM customers
Why it's wrong here
DISTINCT collapses duplicate rows into one, returning unique combinations rather than exposing which values repeat. It tempts analysts wanting a clean list of distinct customers; that suits deduplication output, whereas finding duplicates requires GROUP BY with HAVING COUNT(*) > 1 or a window function flagging repeats.
- ✗
SELECT email, phone, ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY customer_id) AS rn FROM customers WHERE rn > 1
Why it's wrong here
The WHERE clause is evaluated before window functions, so rn is unavailable there and the query errors; filtering must wrap the windowed query in a subquery or CTE. It tempts analysts familiar with ROW_NUMBER for deduplication, which is correct when the outer query filters rn > 1.
- ✓
Use a CTE to assign ROW_NUMBER() and then select rows where rn > 1
Why this is correct
ROW_NUMBER() partitioned by email and phone assigns sequential integers within each duplicate group, so filtering rn > 1 isolates every redundant row. This satisfies the requirement to identify duplicates while retaining full row detail, unlike aggregation alone.
- ✓
SELECT email, phone, COUNT(*) FROM customers GROUP BY email, phone HAVING COUNT(*) > 1
Why this is correct
Grouping by both email and phone collapses identical pairs into single rows, and HAVING COUNT(*) > 1 filters to combinations appearing more than once. This directly satisfies the requirement to identify duplicate customer records across the two specified columns.
About these practice questions
This DA0-002 question is part of Courseiva's 1,004-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official CompTIA exam blueprint
This DA0-002 practice question is part of Courseiva's free CompTIA certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DA0-002 exam.