I had the same thought as you but I think you would want to use row_number not rank. Here is a good example of the difference between rank, and...

Thinking of the aggregate query.. curious if efficiencies could be gained using a WITH Statement or temp table and joining.

With Statement:

With S as (select ProductID,SalesOrderID,SUM(OrderQty) AS OrderQty FROM Sales.SalesOrderDetail GROUP...