WITH Najlepsze2produkty
AS
(
SELECT
	PS.Name "Podkategoria",
	P.Name "Produkt",
	MAX(SOD.LineTotal) "Kwota",
	DENSE_RANK() OVER(PARTITION BY PS.Name ORDER BY MAX(SOD.LineTotal) DESC) "Ranking",
	SUM(MAX(SOD.LineTotal)) OVER(PARTITION BY PS.Name ORDER BY MAX(SOD.LineTotal) DESC) "KwotaNarast"
FROM
	Sales.SalesOrderDetail SOD
	JOIN
	Production.Product P
	ON SOD.ProductID = P.ProductID
	JOIN
	Production.ProductSubcategory PS
	ON P.ProductSubcategoryID = PS.ProductSubcategoryID
GROUP BY
	PS.Name, P.Name
)
SELECT
	Podkategoria,
	Produkt,
	Kwota,
	KwotaNarast "Kwota narastająco"
FROM
	Najlepsze2produkty
WHERE
	Ranking <= 2;