DA0-002 Data Acquisition and Preparation Practice Question

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() gives every row a distinct sequential integer, so tied sales amounts receive different numbers rather than sharing a rank. It tempts because it numbers rows within each category partition, and would be correct when a unique position per row is required regardless of ties.

• ✓

  DENSE_RANK()

  Why this is correct

  DENSE_RANK() assigns identical ranks to tied sales totals yet never skips subsequent numbers, satisfying the no-gaps constraint. RANK() would leave gaps after ties, while ROW_NUMBER() breaks ties arbitrarily. Partitioning by category and ordering by total sales amount yields the required per-category sequence.

• ✗

  RANK()

  Why it's wrong here

  RANK() assigns tied rows the same rank but leaves gaps afterwards, so the next distinct value jumps (1,1,3), violating the no-gaps requirement. It tempts because it does handle ties identically, and would be correct when tied positions should share a rank and subsequent ranks may skip.

• ✗

  NTILE()

  Why it's wrong here

  NTILE() divides rows into a specified number of roughly equal buckets, returning a bucket number rather than a rank based on sales value. It tempts because it partitions and orders rows, and would be correct for splitting products into quartiles or deciles by sales.