SELECT top 1 WITH ties  *
FROM [SalesLT].[Product]
ORDER BY [Weight] DESC
WITH TIES - pokazanie poycji o tej samej wartosci, niby top 1 a zwroci dwa wyniki

SELECT DISTINCT c.Name
FROM [SalesLT].[Product] AS p
JOIN [SalesLT].[ProductCategory] AS c
ON p.ProductCategoryID = c.ProductCategoryID
WHERE [Weight] = (SELECT MAX([Weight]) FROM [SalesLT].[Product])

# Znajdz miasto w ktorym mIEszkaja klienci ktorzy jeszcze nic nie kupili ->
SELECT [City], COUNT(*) AS [liczba klientow]
FROM [SalesLT].[Address] AS a
LEFT JOIN  [SalesLT].[SalesOrderHeader] AS oh
ON a.AddressID = oh.ShipToAddressID
WHERE [CustomerID] IS NULL
GROUP BY [City]
ORDER BY [liczba klientow] DESC

-- Policz cene brutto
-- 1 . cat 20% VAT
-- 2 . cat 10%
-- > 2 . cat 15%

SELECT p.Name, p.ListPrice,
    CASE
        WHEN c.ParentProductCategoryID=1 THEN p.ListPrice * 1.2
        WHEN c.ParentProductCategoryID=2 THEN p.ListPrice * 1.1
        ELSE p.ListPrice * 1.15
    END AS brutto
FROM [SalesLT].[Product] AS p
JOIN [SalesLT].[ProductCategory] AS c
ON p.ProductCategoryID = c.ProductCategoryID
ORDER BY c.[ProductCategoryID]





CREATE VIEW vProductPrice_iJo AS  -> tworzenie widoku

GO
SELECT *
FROM [dbo].[vProductPrice_iJo]
GO





-- cena w zl kiedy jest parzysta
-- numer produktu, konczy sie na L,M albo S
-- ważą mniej niz 1000 i nie wiecej niz 5 000
-- są w sprzedaży
SELECT *
FROM [SalesLT].[Product]
WHERE ROUND([ListPrice], 0) % 2 = 0
AND RIGHT([ProductNumber], 1) IN ('L', 'M', 'S')
AND [SellEndDate] IS NULL
AND ([Weight] BETWEEN 1000 AND 5000 OR [Weight] IS NULL)


SELECT [Name],  [ListPrice],
        MAX([ListPrice]) OVER() AS 'Maks',
        MIN([ListPrice]) OVER() AS 'Min',
        MAX([ListPrice]) OVER() - MIN([ListPrice]) OVER() AS rozstep,
        CUME_DIST() OVER (ORDER BY [ListPrice]) AS freqency,
        PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY [ListPrice]) OVER() AS medianCount,
        PERCENTILE_CONT(0.25) WITHIN GROUP ( ORDER BY [ListPrice]) OVER() AS firstQ,
        PERCENTILE_CONT(0.75) WITHIN GROUP ( ORDER BY [ListPrice]) OVER() AS lastQ,
        STDEV([ListPrice]) OVER() AS odchylenieStandardowe

FROM [SalesLT].[Product]
-- nazwa cena min max mediana kwartyl, rozstep


WITH podzapytanie AS
(SELECT *,
    AVG([ListPrice]) OVER() AS medina,
    2 * STDEV([ListPrice]) OVER() AS sd2
FROM [SalesLT].[Product])

SELECT *
FROM podzapytanie
WHERE [ListPrice] > medina + sd2







-- PONUMERUJ KATEGORIE NA PODSTAWIE SREDNICH CEN PRODUTKOW
WITH cte AS
(SELECT ROW_NUMBER() OVER(ORDER BY avg([ListPrice]) DESC) AS ID, c.Name, avg([ListPrice]) AS avgPrice
FROM [SalesLT].[Product] AS p
JOIN [SalesLT].[ProductCategory] AS c
    ON c.ProductCategoryID = p.ProductCategoryID


GROUP BY c.Name)
--order by avgPrice desc

SELECT *
FROM cte
WHERE ID BETWEEN 10 AND 25
ORDER BY avgPrice DESC