Question

You maintain a Fabric warehouse with a fact table FactSales joined to DimProduct. A report team needs a query that returns, for each product category, the top 3 products by total sales amount, including ties handled so that products with identical totals share the same rank position. Which T-SQL approach should you use?

Accepted Answer

Use RANK() OVER (PARTITION BY category ORDER BY SUM(sales) DESC) in a windowed subquery, then filter WHERE rank <= 3.

Answer

Use ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(sales) DESC) in a subquery, then filter WHERE row_num <= 3.

Answer

Use TOP 3 with ORDER BY SUM(sales) DESC grouped by category in a single SELECT statement.

Answer

Use NTILE(3) OVER (PARTITION BY category ORDER BY SUM(sales) DESC) and filter WHERE tile = 1.