Subquery between three tables using Northwind database with SQL Server

Question

1. Table [Orders] : OrderID(Primary Key), CustomerID
2. Table [Order Details] : OrderID(Primary Key), ProductID(Primary Key), Discount
3. Table [Customers] : CustomerID[Primary Key]

With these three tables, I want to query productID with highest discount for each CustomerID. I need column for ProductID, CustomerID and Discount. How can I solve this problem? All kinds of helps are really appreciated.

Following script I have tried :

select ProductID, a.customerID, 
    (select MAX(discount) 
     from [Order Details] 
     where a.CustomerID=c.customerID
    )
from Orders a 
    join [Order Details]
        on a.OrderID=[Order Details].OrderID 
    join Customers c
        on a.CustomerID=c.CustomerID    
order by customerID

Answer 1

The following query will return to you the productid with maximum discount for each customer. Please note that if for specific customer, you have more than one product that might have the max discount, I you want to return them, then you need to replace ROW_NUMBER() with DENSE_RANK()

  WITH CTE AS 
    (SELECT ProductID, 
           o.CustomerID,
           Discount,
           ROW_NUMBER() OVER(PARTITION BY o.CustomerID ORDER BY Discount DESC) Row_num
    FROM [Order Details] od INNER JOIN Orders o
         ON od.OrderID= o.OrderID
    )
    SELECT ProductID, 
           CustomerID,
         Discount
    FROM CTE
    WHERE Row_num = 1