SQL Question 12

Problem

  • USE Northwind
  • Place customers into ‘quartiles’ (25%) by country based on total dollar amount sold. Dollar amount sold is calculated as (quantity* unitprice) in the order details table

Code

USE Northwind
SELECT c.country, c.contactname, SUM(od.quantity*od.unitprice) 'Total sold $'
, NTIle(4) OVER (partition by c.country Order by SUM(od.quantity*od.unitprice) desc) 'Quartile'
FROM Customers c JOIN orders o ON c.customerID = o.customerid
Join [Order Details] od ON od.orderid = o.orderid
GROUP BY c.contactname, c.country
ORDER BY c.country

Result

Question 12 Result

Question 12 Result

Notes

This question is much like Question 11 except it is using NTILE with a PARTITION BY clause. The NTILE is ORDERED BY the SUM of the quantity * unitprice aliased as 'Total sold $' The whole result set is ORDER BY country.

Comments are closed.