167 ) 7) Most Sold Product by Each State

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Question: Write a query to find the most sold product (by total quantity) in each state using window ranking functions.
Query:

 WITH StateProductSales
AS (
	SELECT s.STATE
		,f.prodid
		,SUM(f.qty) AS total_qty
		,ROW_NUMBER() OVER (
			PARTITION BY s.STATE ORDER BY SUM(f.qty) DESC
			) AS rn
	FROM fact_sales_order f
	INNER JOIN store s ON f.storeid = s.storeid
	GROUP BY s.STATE
		,f.prodid
	) )

-----------------------------------------------------------------------
SELECT STATE
	,prodid
	,total_qty
FROM StateProductSales
WHERE rn = 1;

Sample Output (2 Rows):

StateName	ProductName	TotalQuantitySold
California	Wireless Mouse	1420
Texas	Mechanical Keyboard	980