DECLARE @Customer TABLE (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50))
INSERT @Customer
SELECT 1, 'Jim'
UNION ALL SELECT 2, 'Tom'

DECLARE @Product TABLE (ProductID INT PRIMARY KEY, ProductName VARCHAR(50))
INSERT @Product
SELECT 1, 'Apple'
UNION ALL SELECT 2, 'Peach'

DECLARE @Order TABLE (OrderID INT, CustomerID INT, ProductID INT)
INSERT @Order
SELECT 1, 2, 1
UNION ALL SELECT 1, 2, 1
UNION ALL SELECT 1, 2, 1
UNION ALL SELECT 1, 1, 1
UNION ALL SELECT 1, 1, 1
UNION ALL SELECT 1, 1, 2
UNION ALL SELECT 1, 1, 2
UNION ALL SELECT 1, 1, 2
UNION ALL SELECT 1, 2, 2




SELECT 
	ProductName, 
	CustomerName
FROM
	(
		SELECT 
			ProductName, 
			CustomerName,
			ROW_NUMBER() OVER (PARTITION BY P.ProductName ORDER BY COUNT(*) DESC) AS RowNum
		FROM
			@Order AS O
		INNER JOIN
			@Customer AS C
			ON O.CustomerID = C.CustomerID
		INNER JOIN
			@Product AS P
			ON O.ProductID = P.ProductID
		GROUP BY
			P.ProductName,
			C.CustomerName
	) AS T
WHERE
	T.RowNum = 1