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?

A.
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

B.
SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK()
OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1

C.
SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1

D.
SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC

Explanation:
Use descending (DESC) ordering.
To order by the number of orders we use ORDER BY COUNT(OrderAmount).
Finally a WHERE close is needed: WHERE o.Rnk = 1
Incorrect Answers:
B: The ascending (ASC) sorting would produce the country from which each customer has placed the least
orders.
C: We are interested in the number of the orders, not the amount of the orders. We should use ORDER BY
COUNT(OrderAmount), not ORDER BY OrderAmount.
D: We are only interested in one single post, only the country from which each customer has placed the most
orders. Need to use a WHERE statement (here Where o.Rnk =1 ).