-- zajecia
select
src2.Kategoria,
src2.Podkategoria,
LineTotal,
src2.liczbaSprzedanychProd,
ssss,
DENSE_RANK() over (order by ssss desc)
--srednia,
--ilosc,
--RANK() over (partition by src2.srednia order by src2.srednia) "sredniaNaTransakcje"
 from (
	select
	Kategoria,
	Podkategoria,
	LineTotal,
	liczbaSprzedanychProd,
	ssss
	--sumaProdWtransakcji,
	--avg(sw) over (partition by Kategoria, Podkategoria order by Kategoria, Podkategoria ) "sred"
	--ilosc,
	--sumaProdWtransakcji/ilosc "srednia"
	from (
		select
				cat.Name "Kategoria",
				sub.Name "Podkategoria",
				sum(sod.LineTotal) over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) "LineTotal",
				count(sod.ProductID) over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) "liczbaSprzedanychProd",
				avg(sod.OrderQty) over (partition by sod.SalesOrderDetailID) "ssss",
				--sum(sod.OrderQty) over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) "sumaProdWtransakcji",
				--sum(sod.OrderQty) over(partition by soh.SalesOrderID order by soh.SalesOrderID ) "sumaWszystkichTransakcji",
				--sum(sod.OrderQty) over(partition by soh.SalesOrderID order by soh.SalesOrderID ) sw,
				ROW_NUMBER() over (partition by cat.Name,sub.Name order by cat.Name,sub.Name ) RowNumber
			from
				(select * from AdventureWorks2014.Sales.SalesOrderDetail) sod join
				(select ProductSubcategoryID, ListPrice, ProductID from AdventureWorks2014.Production.Product) prod on sod.ProductID=prod.ProductID join
				(select ProductCategoryID, ProductSubcategoryID, Name from AdventureWorks2014.Production.ProductSubcategory) sub on prod.ProductSubcategoryID=sub.ProductSubcategoryID join
				(select ProductCategoryID, Name from AdventureWorks2014.Production.ProductCategory) cat on sub.ProductCategoryID=cat.ProductCategoryID
	) src
	where src.RowNumber = 1
) src2