Which Transact-SQL query do you use?

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?

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 CustomerID
ORDER 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:
Verified answer as correct.



Leave a Reply 2

Your email address will not be published. Required fields are marked *


riemi

riemi

I think A is the best answer available but it should be “PARTITION BY ShippingCountry”.

riemi

riemi

No, Iva had an error in reasoning – A is correct.

I actually tested it on AdventureWorks2012 database:

SELECT c.CustomerID, o.TerritoryID
FROM [Sales].[Customer] c
INNER JOIN
(SELECT CustomerID, TerritoryID,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(*) DESC) AS Rnk
FROM [Sales].[SalesOrderHeader] o
GROUP BY CustomerID, TerritoryID) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
order by c.CustomerID