DA0-002 Data Acquisition and Preparation Practice Question
A data analyst needs to identify the top 3 most frequent product categories from a sales table. Which SQL techniques can be used to achieve this? (Choose two.)
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
✓
SELECT category, COUNT(*) as cnt FROM sales GROUP BY category QUALIFY DENSE_RANK() OVER (ORDER BY cnt DESC) <= 3
Both GROUP BY with ORDER BY and LIMIT, and using a window function like DENSE_RANK() to rank categories by count and filter top 3, are valid. HAVING is for filtering groups after aggregation, but without ORDER BY and LIMIT, it doesn't give top 3. A subquery with COUNT(*)>3 would get categories with count >3, not top 3. LEFT JOIN is irrelevant.
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 category, COUNT(*) as cnt FROM sales GROUP BY category QUALIFY DENSE_RANK() OVER (ORDER BY cnt DESC) <= 3
Why this is correct
Using window function DENSE_RANK() with QUALIFY (or in a subquery) returns top 3 categories including ties.
- ✓
GROUP BY category ORDER BY COUNT(*) DESC LIMIT 3
Why this is correct
Correctly limits to top 3 categories by count.
- ✗
SELECT category FROM sales LEFT JOIN (SELECT category FROM sales GROUP BY category HAVING COUNT(*) > 3) AS t ON sales.category = t.category
Why it's wrong here
Not a valid method to get top 3; complexity without correct logic.
- ✗
SELECT DISTINCT category FROM sales ORDER BY category DESC LIMIT 3
Why it's wrong here
Orders by category alphabetically, not by frequency.
- ✗
SELECT category, COUNT(*) FROM sales GROUP BY category HAVING COUNT(*) > 3
Why it's wrong here
Returns categories with count greater than 3, not necessarily top 3.
Go deeper
Related to this question
About these practice questions
One of 986 original DA0-002 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.