Home / DE Professional practice test / Data Transformation, Cleansing, and Quality / Question

Databricks Data Engineer Professional · free practice question

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…

Exam-style question on Data Transformation, Cleansing, and Quality from the free DE Professional practice test, with the answer and a full explanation below.

Updated November 16, 2026

Data Transformation, Cleansing, and Quality Medium

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?

  1. AAggregate revenue per (category, product_id), then compute dense_rank() over PARTITION BY category ORDER BY total_revenue DESC and filter dense_rank <= 3.
  2. BAggregate revenue per (category, product_id), then compute row_number() over PARTITION BY category ORDER BY total_revenue DESC and filter row_number <= 3.
  3. CSkip window functions: GROUP BY category and use ORDER BY total_revenue DESC LIMIT 3 to automatically return the top 3 per category.
  4. DUse rank() and filter rank <= 3; rank() never produces gaps, so it always returns exactly three rows per category.
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.

Take the full DE Professional practice test free →

Would you pass today?

This question is one of 60 in the free, timed Databricks Data Engineer Professional practice test. Take it and get a section-by-section score.

Start the free test →