A Delta table sales contains category, product_id, and revenue. A report needs the top 3 products per category by total revenue, but must include ties (for example, if the 3rd and 4th products have the same revenue, both should appear). Which approach best satisfies the requirement?
Answer & explanation
Correct answer: A
WHY A: dense_rank() assigns the same rank to ties and does not create gaps, so filtering dense_rank <= 3 includes all products tied within the top-3 ranks. WHY NOT B: row_number() breaks ties arbitrarily, which can exclude tied items that should be included. WHY NOT C: LIMIT 3 applies to the whole result set, not per category. WHY NOT D: rank() *does* produce gaps when there are ties, so rank <= 3 can return fewer than 3 ranks worth of data depending on ties; it also doesn’t guarantee exactly three rows.