DA0-002 Data Acquisition and Preparation Practice Question
A data analyst is using a window function to assign a unique rank to each employee within their department based on salary, with ties receiving the same rank and leaving gaps. Which function should be used?
⚠ Common exam trap
Many exam-takers confuse RANK() with DENSE_RANK(); candidates often forget that RANK() leaves gaps after ties, while DENSE_RANK() does not.
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
✓
RANK()
RANK() assigns a rank to each row within a partition based on the ORDER BY clause, giving tied values the same rank and then skipping subsequent ranks (e.g., 1, 2, 2, 4). This matches the requirement of 'ties receiving the same rank and leaving gaps.' DENSE_RANK() would not leave gaps, ROW_NUMBER() would not assign ties, and NTILE() divides rows into buckets rather than ranking them.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
DENSE_RANK()
Why it's wrong here
DENSE_RANK() gives tied salaries the same rank but assigns consecutive ranks with no gaps, so it fails the gap requirement. It is tempting because DENSE_RANK() genuinely handles ties correctly, but the stem explicitly requires gaps after ties, which RANK() produces.
- ✓
RANK()
Why this is correct
RANK() assigns identical ranks to tied salary values but skips subsequent numbers, producing gaps such as 1, 2, 2, 4. This matches the stem's requirement for ties sharing a rank while leaving gaps, unlike DENSE_RANK or ROW_NUMBER.
- ✗
ROW_NUMBER()
Why it's wrong here
ROW_NUMBER() assigns sequential integers with no duplicates, so tied salaries receive different numbers and no gaps appear. It is tempting because ROW_NUMBER() genuinely produces unique sequential numbering for pagination or deduplication, but the stem requires ties to share a rank with gaps preserved, which RANK() provides.
- ✗
NTILE()
Why it's wrong here
NTILE() divides rows into a specified number of roughly equal buckets, so it never leaves gaps between ranks. It is tempting because NTILE() genuinely ranks rows into quartiles or deciles for distribution analysis, but the stem requires tied salaries to share a rank with gaps preserved, which RANK() provides.
About these practice questions
One of 1,004 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 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.