Question

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions: Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?

Answer 1

Answer: SELECT c.CustomerID, c.CustomerName, o.ShippingCountry 

FROM Customer c 

INNER JOIN 

(SELECT CustomerID, ShippingCountry, 

RANK() OVER (PARTITION BY CustomerID 

ORDER BY COUNT(OrderAmount) DESC) AS Rnk 

FROM Orders 

GROUP BY CustomerID, ShippingCountry) AS o 

ON c.CustomerID = o.CustomerID 

WHERE o.Rnk = 1