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