DA0-002 Data Acquisition and Preparation Practice Question
A data analyst is writing a query to rank products by total sales within each category, showing dense rank and avoiding gaps. Which window function should be used?
⚠ Common exam trap
The trap is the subtle difference between RANK() and DENSE_RANK() — candidates who remember 'RANK' but forget the gap behavior pick RANK() and fail the 'avoiding gaps' requirement.
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
✓
DENSE_RANK()
DENSE_RANK() assigns ranks without gaps when ties occur — if two products tie for rank 1, the next product gets rank 2, not 3. This matches the requirement to 'avoid gaps' while still assigning equal ranks to ties. It is used with OVER (PARTITION BY category ORDER BY total_sales DESC).
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
ROW_NUMBER()
Why it's wrong here
ROW_NUMBER() assigns sequential integers without repeating values, so tied sales figures receive different ranks and no dense grouping occurs. It is tempting for pagination or deduplication, where unique sequential numbering is required, but the stem demands tied values share the same rank without gaps.
- ✓
DENSE_RANK()
Why this is correct
DENSE_RANK() assigns consecutive ranks without gaps when ties occur, directly satisfying the requirement to avoid gaps while ranking products by total sales within each category. Unlike ROW_NUMBER(), which gives arbitrary distinct values to tied rows, DENSE_RANK() preserves equal ranking for ties and continues sequentially, matching the dense rank constraint.
- ✗
NTILE()
Why it's wrong here
NTILE() divides rows into a specified number of roughly equal buckets, not ranks based on sales values, so ties are split arbitrarily across buckets. It is tempting for quartile or percentile analysis, where equal-sized groups are wanted, but the stem requires dense ranking of tied sales values.
- ✗
RANK()
Why it's wrong here
RANK() leaves gaps after ties, so two products sharing rank 1 cause the next to receive rank 3, violating the no-gaps requirement. It is tempting because it does rank by sales within partitions, but the stem explicitly asks for dense rank, which DENSE_RANK() provides.
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.