USE [AdventureWorks2014];
go

SELECT	sub.Name, prd.Name, YEAR(soh.OrderDate),
		sod.OrderQty

FROM	[Sales].[SalesOrderDetail] sod
		JOIN [Sales].[SalesOrderHeader] soh
		ON (sod.SalesOrderID = soh.SalesOrderID)
		JOIN [Production].[Product] prd
		ON (sod.ProductID = prd.ProductID)
		JOIN [Production].[ProductSubcategory] sub
		ON (prd.ProductSubcategoryID = sub.ProductSubcategoryID)

ORDER BY 1, 2, 3;
go


-- Wrap the previous in a cte
WITH UnitSold (categoryName, productName, yearSold, units)
AS (SELECT	sub.Name, prd.Name, YEAR(soh.OrderDate),
		sod.OrderQty

FROM	[Sales].[SalesOrderDetail] sod
		JOIN [Sales].[SalesOrderHeader] soh
		ON (sod.SalesOrderID = soh.SalesOrderID)
		JOIN [Production].[Product] prd
		ON (sod.ProductID = prd.ProductID)
		JOIN [Production].[ProductSubcategory] sub
		ON (prd.ProductSubcategoryID = sub.ProductSubcategoryID))

SELECT	*
FROM	UnitSold;
go


-- Use the previous CTE to determine the different years a product was sold
WITH UnitSold (categoryName, productName, yearSold, units)
AS (SELECT	sub.Name, prd.Name, YEAR(soh.OrderDate),
		sod.OrderQty

FROM	[Sales].[SalesOrderDetail] sod
		JOIN [Sales].[SalesOrderHeader] soh
		ON (sod.SalesOrderID = soh.SalesOrderID)
		JOIN [Production].[Product] prd
		ON (sod.ProductID = prd.ProductID)
		JOIN [Production].[ProductSubcategory] sub
		ON (prd.ProductSubcategoryID = sub.ProductSubcategoryID))

SELECT	DISTINCT yearSold
FROM	UnitSold;
go


-- Pivot the previous CTE to display the total products sold per category
-- for each year
WITH UnitSold (categoryName, yearSold, units)
AS (SELECT	sub.Name, YEAR(soh.OrderDate),
		sod.OrderQty

FROM	[Sales].[SalesOrderDetail] sod
		JOIN [Sales].[SalesOrderHeader] soh
		ON (sod.SalesOrderID = soh.SalesOrderID)
		JOIN [Production].[Product] prd
		ON (sod.ProductID = prd.ProductID)
		JOIN [Production].[ProductSubcategory] sub
		ON (prd.ProductSubcategoryID = sub.ProductSubcategoryID))

SELECT	categoryName, [2011], [2012], [2013], [2014]
FROM	UnitSold
		PIVOT (SUM(units) FOR yearSold in ([2011], [2012], [2013], [2014])) AS pvt;
go


-- Rank each product per category based on the total units sold
WITH UnitSold (categoryName, productName, yearSold, units)
AS (SELECT	sub.Name, prd.Name, YEAR(soh.OrderDate),
		sod.OrderQty

FROM	[Sales].[SalesOrderDetail] sod
		JOIN [Sales].[SalesOrderHeader] soh
		ON (sod.SalesOrderID = soh.SalesOrderID)
		JOIN [Production].[Product] prd
		ON (sod.ProductID = prd.ProductID)
		JOIN [Production].[ProductSubcategory] sub
		ON (prd.ProductSubcategoryID = sub.ProductSubcategoryID))

SELECT	categoryName, productName, SUM(units) as 'Units sold',
		RANK() OVER(PARTITION BY categoryName ORDER BY SUM(units) DESC) AS 'Rank',
		DENSE_RANK() OVER(PARTITION BY categoryName ORDER BY SUM(units) DESC) AS 'Dense rank'

FROM	UnitSold
GROUP BY categoryName, productName
ORDER BY 1 ASC, 4 ASC;
go



-- Revise the previous to include a 10 row pagination
WITH UnitSold (categoryName, productName, yearSold, units)
AS (SELECT	sub.Name, prd.Name, YEAR(soh.OrderDate),
		sod.OrderQty

FROM	[Sales].[SalesOrderDetail] sod
		JOIN [Sales].[SalesOrderHeader] soh
		ON (sod.SalesOrderID = soh.SalesOrderID)
		JOIN [Production].[Product] prd
		ON (sod.ProductID = prd.ProductID)
		JOIN [Production].[ProductSubcategory] sub
		ON (prd.ProductSubcategoryID = sub.ProductSubcategoryID))

SELECT	categoryName, productName, SUM(units) as 'Units sold',
		RANK() OVER(PARTITION BY categoryName ORDER BY SUM(units) DESC) AS 'Rank',
		DENSE_RANK() OVER (PARTITION BY categoryName ORDER BY SUM(units) DESC) AS 'Dense rank',
		ROW_NUMBER() OVER (ORDER BY categoryName ASC, SUM(units) DESC)

FROM	UnitSold
GROUP BY categoryName, productName
ORDER BY 1 ASC, 4 ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS only;
go


-- Unpivot the pivot statement
WITH UnitSold (categoryName, yearSold, units)
AS (SELECT	sub.Name, YEAR(soh.OrderDate),
		sod.OrderQty
	FROM	[Sales].[SalesOrderDetail] sod
		JOIN [Sales].[SalesOrderHeader] soh
		ON (sod.SalesOrderID=soh.SalesOrderID)
		JOIN [Production].[Product] prd
		ON (sod.productId=prd.productId)
		JOIN [Production].[ProductSubcategory] sub
		ON (prd.ProductSubcategoryID=sub.ProductSubcategoryID)),
pivotTable (categoryName, [2011], [2012], [2013], [2014])
AS (SELECT	categoryName, [2011], [2012],[2013],[2014]
	FROM	UnitSold
			PIVOT (SUM(units) FOR yearSold IN
			 ([2011],[2012],[2013],[2014])) AS pvt)
select	*
from	pivotTable
		UNPIVOT (unitsSold FOR
		yearSold IN ([2011],[2012],[2013],[2014])) AS unpvt;
go