SQL And Northwind

Question

I have a query where i can find all the products, all the customers that bought each product and the quantity.

select OD.ProductID, OD.Quantity, O.CustomerID
from dbo.[Order Details] OD inner join dbo.Orders O on OD.OrderID = O.OrderID
Order by OD.ProductID ASC, OD.Quantity DESC

But what i need is to now which customer bought the most of each product. How can I do it?

Answer 1

You can use top 1 with ties with rank window function:

select top 1 with ties OD.ProductID,
    OD.Quantity,
    O.CustomerID
from dbo.[Order Details] OD
inner join dbo.Orders O on OD.OrderID = O.OrderID
order by rank() over (
        partition by OD.ProductID order by OD.Quantity desc
        );

The above will return multiple rows per productId if there are multiple customers with max quantity ordered for that product.

If you want to get only one row, you can use row_number:

select top 1 with ties OD.ProductID,
    OD.Quantity,
    O.CustomerID
from dbo.[Order Details] OD
inner join dbo.Orders O on OD.OrderID = O.OrderID
order by row_number() over (
        partition by OD.ProductID order by OD.Quantity desc
        );

You can also do this without top:

select *
from (
    select OD.ProductID,
        OD.Quantity,
        O.CustomerID,
        row_number() over (
            partition by OD.ProductID order by OD.Quantity desc
            ) as rn
    from dbo.[Order Details] OD
    inner join dbo.Orders O on OD.OrderID = O.OrderID
    ) t
where rn = 1;