--Zad 1 ROLLUP

SELECT
	P.FirstName+' '+P.LastName "Nazwa",
	YEAR(OrderDate) "Rok",
	CONCAT('$', SUM(SubTotal)) "Suma wydatkow"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Person.Person P ON SOH.CustomerID = P.BusinessEntityID
GROUP BY ROLLUP(P.FirstName+' '+P.LastName, YEAR(OrderDate))
ORDER BY 1;


--Zad 1 CUBE
SELECT
	MIN(P.FirstName+' '+P.LastName) "Nazwa",
	YEAR(OrderDate) "Rok",
	CONCAT('$', SUM(SubTotal)) "Suma wydatkow"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Person.Person P ON SOH.CustomerID = P.BusinessEntityID
GROUP BY CUBE(YEAR(OrderDate), P.FirstName+' '+P.LastName)
ORDER BY 1;


--Zad 1 GROUPING SET
SELECT
	P.FirstName+' '+P.LastName "Nazwa",
	YEAR(OrderDate) "Rok",
	CONCAT('$', SUM(SubTotal)) "Suma wydatkow"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Person.Person P ON SOH.CustomerID = P.BusinessEntityID
GROUP BY GROUPING SETS((P.FirstName+' '+P.LastName, YEAR(OrderDate)), (P.FirstName+' '+P.LastName))
ORDER BY 1;

--Zad 2 ROLL UP
SELECT
	MIN(PC.Name) "Kategoria",
	P.Name "Produkt",
	YEAR(SOH.OrderDate) "Rok",
	CONCAT('$', SUM(SOD.UnitPrice * SOD.UnitPriceDiscount))"Kwota"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Sales.SalesOrderDetail SOD
	ON SOH.SalesOrderID = SOD.SalesOrderID
	JOIN
	Production.Product P
	ON SOD.ProductID = P.ProductID
	JOIN
	Production.ProductSubcategory PS
	ON P.ProductSubcategoryID = PS.ProductSubcategoryID
	JOIN
	Production.ProductCategory PC
	ON PS.ProductCategoryID = PC.ProductCategoryID
GROUP BY ROLLUP(P.Name, YEAR(SOH.OrderDate));

--Zad 2 CUBE
SELECT
	PC.Name "Kategoria",
	P.Name "Produkt",
	YEAR(SOH.OrderDate) "Rok",
	CONCAT('$', SUM(SOD.UnitPrice * SOD.UnitPriceDiscount))"Kwota"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Sales.SalesOrderDetail SOD
	ON SOH.SalesOrderID = SOD.SalesOrderID
	JOIN
	Production.Product P
	ON SOD.ProductID = P.ProductID
	JOIN
	Production.ProductSubcategory PS
	ON P.ProductSubcategoryID = PS.ProductSubcategoryID
	JOIN
	Production.ProductCategory PC
	ON PS.ProductCategoryID = PC.ProductCategoryID
GROUP BY CUBE(YEAR(SOH.OrderDate), P.Name, PC.Name)
ORDER BY 2;

--Zad 2 GROUPING SETS
SELECT
	MIN(PC.Name) "Kategoria",
	P.Name "Produkt",
	YEAR(SOH.OrderDate) "Rok",
	CONCAT('$', SUM(SOD.UnitPrice * SOD.UnitPriceDiscount))"Kwota"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Sales.SalesOrderDetail SOD
	ON SOH.SalesOrderID = SOD.SalesOrderID
	JOIN
	Production.Product P
	ON SOD.ProductID = P.ProductID
	JOIN
	Production.ProductSubcategory PS
	ON P.ProductSubcategoryID = PS.ProductSubcategoryID
	JOIN
	Production.ProductCategory PC
	ON PS.ProductCategoryID = PC.ProductCategoryID
GROUP BY GROUPING SETS((P.Name), (P.Name, YEAR(SOH.OrderDate)))
ORDER BY 2;

--Zad 3
SELECT DISTINCT
	PC.Name "Nazwa",
	YEAR(SOH.OrderDate) "Rok",
	SUM(SOH.SubTotal) OVER(PARTITION BY YEAR(SOH.OrderDate)) / (SUM(SOH.SubTotal) OVER ()) * 100 "Procent"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Sales.SalesOrderDetail SOD
	ON SOH.SalesOrderID = SOD.SalesOrderID
	JOIN
	Production.Product P
	ON SOD.ProductID = P.ProductID
	JOIN
	Production.ProductSubcategory PS
	ON P.ProductSubcategoryID = PS.ProductSubcategoryID
	JOIN
	Production.ProductCategory PC
	ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE PC.Name = 'Bikes'
ORDER BY 2;

--Zad 4
SELECT DISTINCT
	CustomerID "Klient",
	YEAR(OrderDate) "Rok",
	COUNT(OrderDate) OVER(PARTITION BY CustomerID ORDER BY YEAR(OrderDate)) "Liczba zamówień narastająco"
FROM
	Sales.SalesOrderHeader
ORDER BY
	1, 3;

--Zad 5
SELECT
	P.FirstName+' '+P.LastName "Imię i nazwisko",
	YEAR(SOH.OrderDate) "Rok",
	MONTH(SOH.OrderDate) "Miesiąc",
	SUM(COUNT(SOH.SalesOrderID)) OVER(PARTITION BY SOH.SalesPersonID, YEAR(SOH.OrderDate)) "W roku",
	SUM(COUNT(SOH.SalesOrderID)) OVER(PARTITION BY SOH.SalesPersonID, YEAR(SOH.OrderDate) ORDER BY MONTH(SOH.OrderDate)) "W roku narastająco",
	SUM(COUNT(SOH.SalesOrderID)) OVER(PARTITION BY SOH.SalesPersonID, YEAR(SOH.OrderDate) ORDER BY MONTH(SOH.OrderDate) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) "Obecny i poprzedni miesiąc"
FROM
	Sales.SalesOrderHeader SOH
	JOIN
	Person.Person P ON SOH.SalesPersonID = P.BusinessEntityID
GROUP BY
	P.FirstName+' '+P.LastName, SOH.SalesPersonID,YEAR(SOH.OrderDate) , MONTH(SOH.OrderDate)
ORDER BY 1, 2, 3;

--Zad 6

WITH MaxKategorie
AS
(
	SELECT
		PC.Name "Kategoria",
		PS.Name "Podkategoria",
		MAX(SOD.UnitPrice) "MaxCena"
	FROM
		Sales.SalesOrderDetail SOD
		JOIN
		Production.Product P
		ON SOD.ProductID = P.ProductID
		JOIN
		Production.ProductSubcategory PS
		ON P.ProductSubcategoryID = PS.ProductSubcategoryID
		JOIN
		Production.ProductCategory PC
		ON PS.ProductCategoryID = PC.ProductCategoryID
	GROUP BY
		PC.Name, PS.Name
)
SELECT DISTINCT
	MK.Kategoria "Kategoria",
	SUM(MAX(MK.MaxCena)) OVER(PARTITION BY MK.Kategoria) "Suma"
FROM
	MaxKategorie MK
GROUP BY
	MK.Kategoria, MK.Podkategoria;

--LUB
SELECT DISTINCT
	PC.Name "Kategoria",
	SUM(MAX(SOD.UnitPrice)) OVER(PARTITION BY PC.ProductCategoryID) "Suma"
FROM
	Sales.SalesOrderDetail SOD
	JOIN
	Production.Product P
	ON SOD.ProductID = P.ProductID
	JOIN
	Production.ProductSubcategory PS
	ON P.ProductSubcategoryID = PS.ProductSubcategoryID
	JOIN
	Production.ProductCategory PC
	ON PS.ProductCategoryID = PC.ProductCategoryID
GROUP BY PC.Name, PC.ProductCategoryID, PS.ProductSubcategoryID;