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?

you welcome ops by bad i missed the per customer part i guess this one would help https://www.sqlines.com/mysql/how-to/get_top_n_each_group just change the where to be less or equal 3 incase if some customer only has 1 or 2 totaldue value — , Aug 14 '22 at 13:12
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Aug 14 '22 at 17:45
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Aug 14 '22 at 21:24

score 0 · Answer 1 · answered Aug 14 '22 at 13:46

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

How to query top 3 sales per customer grouped by product

1 Answers1