How to query top 3 sales per customer grouped by product

Question

I'm new to this. So far here is my code, I have joined 4 tables (AdventureWorks database)

Total Due = Sales amount,

Select productsubcategory.ProductCategoryID, product.ProductID,
salesorderdetail.SalesOrderID,
SalesOrderHeader.CustomerID,
SalesOrderHeader.TotalDue 
From product
Inner Join productsubcategory ON product.ProductSubcategoryID=productsubcategory.ProductSubcategoryID
Inner Join salesorderdetail ON salesorderdetail.ProductID=product.ProductID
Inner Join SalesOrderHeader ON SalesOrderHeader.SalesOrderID=salesorderdetail.SalesOrderID

Here are my results :

ProductCategoryID	ProductID	SalesOrderID	CustomerID	TotalDue
4	707	5858	884	399938

What do I need to add to my code to only pull top 3 totaldue amounts per customer?

Answer 1

use a windowing function. I'm using row_number, ordered by the total due with highest first and grouped by the customer id. this will give you a ranking for each row. then pick the ones that are 3 or less

with t as (Select productsubcategory.ProductCategoryID, product.ProductID,
    salesorderdetail.SalesOrderID,
    SalesOrderHeader.CustomerID,
    SalesOrderHeader.TotalDue,
    row_number() over (partition by salesOrderHeader.CustomerId, order by SalesOrderHeader.TotalDue desc) as rn 
    From product
    Inner Join productsubcategory ON product.ProductSubcategoryID=productsubcategory.ProductSubcategoryID
    Inner Join salesorderdetail ON salesorderdetail.ProductID=product.ProductID
    Inner Join SalesOrderHeader ON SalesOrderHeader.SalesOrderID=salesorderdetail.SalesOrderID)
    select * from t where rn <= 3