CREATE TABLE KartaLojalnosciowa(
    Imie Name NOT NULL,
    NazwiskoKlienta Name NOT NULL,
    LiczbaTransakcji NAME NOT NULL,
    LacznaKwotaTransakcji INT,
    KolorKarty NVARCHAR(9) NOT NULL
)


INSERT INTO KartaLojalnosciowa
SELECT query.FirstName[Imie], LastName[Nazwisko], query.numberOfTransactions[Liczba transakcji], query.transactionsAmmount[Laczna kwota transakcji], query.color[Kolor karty] FROM(
SELECT query.customerID, FirstName, LastName, query.numberOfTransactions, query.transactionsAmmount, query.color
FROM (SELECT subQuery.CustomerID , SUM(subQuery.numberOfTransactions) numberOfTransactions, SUM(subQuery.transactionsAmmount) transactionsAmmount, 'Platynowa' color
FROM (SELECT COUNT(CustomerID) numberOfTransactions, CustomerID, YEAR(OrderDate) orderYear, SUM(SubTotal) transactionsAmmount
    FROM Sales.SalesOrderHeader
    WHERE SubTotal > (SELECT AVG(SubTotal)*1.5
    FROM Sales.SalesOrderHeader)
    GROUP BY CustomerID, YEAR(OrderDate)
    HAVING COUNT(*) >= 2
) subQuery
GROUP BY subQuery.CustomerID
HAVING COUNT(subQuery.CustomerID) = (
    SELECT COUNT(DISTINCT YEAR(OrderDate))
    FROM Sales.SalesOrderHeader
))query JOIN
    Sales.Customer ON query.CustomerID = Customer.CustomerID
    JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID

UNION

SELECT sub_query.customerID, FirstName, LastName, numberOfTransactions, transactionsAmmount, color
FROM(
SELECT CustomerID, COUNT(*) numberOfTransactions, SUM(SubTotal) transactionsAmmount, 'Zlota' color
FROM Sales.SalesOrderHeader
WHERE SubTotal > (
        SELECT AVG(SubTotal)*1.5
        FROM Sales.SalesOrderHeader)
		AND CustomerID NOT IN (SELECT query.customerID
FROM (SELECT subQuery.CustomerID , SUM(subQuery.numberOfTransactions) numberOfTransactions, SUM(subQuery.transactionsAmmount) transactionsAmmount, 'Platynowa' color
FROM (SELECT COUNT(CustomerID) numberOfTransactions, CustomerID, YEAR(OrderDate) orderYear, SUM(SubTotal) transactionsAmmount
    FROM Sales.SalesOrderHeader
    WHERE SubTotal > (SELECT AVG(SubTotal)*1.5
    FROM Sales.SalesOrderHeader)
    GROUP BY CustomerID, YEAR(OrderDate)
    HAVING COUNT(*) >= 2
) subQuery
GROUP BY subQuery.CustomerID
HAVING COUNT(subQuery.CustomerID) = (
    SELECT COUNT(DISTINCT YEAR(OrderDate))
    FROM Sales.SalesOrderHeader
))query JOIN
    Sales.Customer ON query.CustomerID = Customer.CustomerID
    JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID)
GROUP BY CustomerID
HAVING COUNT(*) >= 2) sub_query JOIN Sales.Customer ON sub_query.CustomerID = Customer.CustomerID
    JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID

UNION
SELECT sub_query.customerID, FirstName, LastName, numberOfTransactions, transactionsAmmount, color
FROM(
SELECT S.CustomerID, COUNT(*) numberOfTransactions, SUM(SubTotal) transactionsAmmount, 'Srebrna' color
FROM Sales.SalesOrderHeader S
WHERE NOT EXISTS (SELECT query.customerID, FirstName, LastName, query.numberOfTransactions, query.transactionsAmmount, query.color
FROM (SELECT subQuery.CustomerID , SUM(subQuery.numberOfTransactions) numberOfTransactions, SUM(subQuery.transactionsAmmount) transactionsAmmount, 'Platynowa' color
FROM (SELECT COUNT(CustomerID) numberOfTransactions, CustomerID, YEAR(OrderDate) orderYear, SUM(SubTotal) transactionsAmmount
    FROM Sales.SalesOrderHeader
    WHERE SubTotal > (SELECT AVG(SubTotal)*1.5
    FROM Sales.SalesOrderHeader)
    GROUP BY CustomerID, YEAR(OrderDate)
    HAVING COUNT(*) >= 2
) subQuery
GROUP BY subQuery.CustomerID
HAVING COUNT(subQuery.CustomerID) = (
    SELECT COUNT(DISTINCT YEAR(OrderDate))
    FROM Sales.SalesOrderHeader
))query JOIN
    Sales.Customer ON query.CustomerID = Customer.CustomerID
    JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID
WHERE query.CustomerID = S.CustomerID)
    AND NOT EXISTS (SELECT sub_query.customerID, FirstName, LastName, numberOfTransactions, transactionsAmmount, color
FROM(
SELECT CustomerID, COUNT(*) numberOfTransactions, SUM(SubTotal) transactionsAmmount, 'Zlota' color
FROM Sales.SalesOrderHeader
WHERE SubTotal > (
        SELECT AVG(SubTotal)*1.5
        FROM Sales.SalesOrderHeader)
		AND CustomerID NOT IN (SELECT query.customerID
FROM (SELECT subQuery.CustomerID , SUM(subQuery.numberOfTransactions) numberOfTransactions, SUM(subQuery.transactionsAmmount) transactionsAmmount, 'Platynowa' color
FROM (SELECT COUNT(CustomerID) numberOfTransactions, CustomerID, YEAR(OrderDate) orderYear, SUM(SubTotal) transactionsAmmount
    FROM Sales.SalesOrderHeader
    WHERE SubTotal > (SELECT AVG(SubTotal)*1.5
    FROM Sales.SalesOrderHeader)
    GROUP BY CustomerID, YEAR(OrderDate)
    HAVING COUNT(*) >= 2
) subQuery
GROUP BY subQuery.CustomerID
HAVING COUNT(subQuery.CustomerID) = (
    SELECT COUNT(DISTINCT YEAR(OrderDate))
    FROM Sales.SalesOrderHeader
))query JOIN
    Sales.Customer ON query.CustomerID = Customer.CustomerID
    JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID)
GROUP BY CustomerID
HAVING COUNT(*) >= 2) sub_query JOIN Sales.Customer ON sub_query.CustomerID = Customer.CustomerID
    JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID
WHERE sub_query.CustomerID = S.CustomerID)
GROUP BY CustomerID
HAVING COUNT(*) >= 5)
sub_query JOIN Sales.Customer ON sub_query.CustomerID = Customer.CustomerID
    JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID)query


 TRUNCATE TABLE KartaLojalnosciowa
 DROP TABLE KartaLojalnosciowa
 SELECT * FROM KartaLojalnosciowa
order by 2;