HomeWork

--1)
SELECT CUST_FIRST_NAME, CUST_LAST_NAME
FROM CUSTOMERS
WHERE CUST_GENDER = 'F'
AND CUST_MARITAL_STATUS = 'married'
AND COUNTRY_ID NOT IN ('JP', 'BR', 'IT')
AND CUST_CREDIT_LIMIT = (SELECT MIN(CUST_CREDIT_LIMIT) FROM CUSTOMERS)


--2)
SELECT
'NAME: '||
cust_first_name ||
'; city: ' ||
cust_city ||
'; address: '||
cust_street_address||
'; number:'||
cust_main_phone_number||
'; email: '||
cust_email||
';' AS OneColumn
FROM (SELECT *
        FROM CUSTOMERS
        WHERE CUST_MAIN_PHONE_NUMBER LIKE '%77') t
WHERE LENGTH(CUST_STREET_ADDRESS) = (SELECT MAX(LENGTH(t.CUST_STREET_ADDRESS))
        FROM (SELECT *
        FROM CUSTOMERS
        WHERE CUST_MAIN_PHONE_NUMBER LIKE '%77') t)

--3)

WITH
etc0 AS (
    SELECT * -- Customers старше 1980
    FROM CUSTOMERS
    WHERE CUST_YEAR_OF_BIRTH > 1980),
etc1 AS ( -- Минимальная цена продукта для категорий
    SELECT MIN(PROD_LIST_PRICE) AS minprc
    FROM Products p
    WHERE PROD_SUBCATEGORY = 'Shirts - Boys'
    OR PROD_SUBCATEGORY = 'Shirts - Girls'),
etc2 AS ( -- Все продукты с этой ценой
    SELECT *
    FROM Products p
    WHERE (PROD_SUBCATEGORY = 'Shirts - Boys'
    OR PROD_SUBCATEGORY = 'Shirts - Girls')
    AND PROD_LIST_PRICE = (SELECT minprc FROM etc1)
    ORDER BY PROD_LIST_PRICE),
etc3 AS ( -- Добавляем к etc2 поле с CUST_ID
    SELECT CUST_ID, etc2.*
    FROM SALES s
    JOIN etc2
    ON etc2.PROD_ID=s.PROD_ID)
SELECT *
FROM etc0 -- Customets старше 1980
WHERE CUST_ID IN (
SELECT CUST_ID
FROM etc3)

--4)
SELECT *
FROM CUSTOMERS c1
WHERE EXISTS
(
SELECT *
FROM CUSTOMERS c2
WHERE c1.CUST_ID=c2.CUST_ID
AND CUST_INCOME_LEVEL LIKE 'D:%'
AND CUST_GENDER = 'M'
AND CUST_MARITAL_STATUS IS NULL
AND (COUNTRY_ID IN ('US','DE')))

--5)
WITH
etc1 AS (
    SELECT PROD_ID, PROD_LIST_PRICE
    FROM Products p),
etc2 AS(
    SELECT PROD_ID, CUST_ID
    FROM Sales s),
etc3 AS (
    SELECT CUST_ID, COUNTRY_ID
    FROM Customers),
etc4 AS (
    SELECT COUNTRY_ID, COUNTRY_NAME
    FROM Countries),
etc5 AS(
    SELECT CUST_ID, PROD_LIST_PRICE
    FROM etc1
    JOIN etc2
    ON etc1.PROD_ID = etc2.PROD_ID),
etc6 AS(
    SELECT COUNTRY_ID, PROD_LIST_PRICE
    FROM etc5
    JOIN etc3
    ON etc5.CUST_ID = etc3.CUST_ID),
etc7 AS(
    SELECT COUNTRY_NAME, PROD_LIST_PRICE
    FROM etc4
    JOIN etc6
    ON etc4.COUNTRY_ID = etc6.COUNTRY_ID)
SELECT COUNTRY_NAME, AVG(PROD_LIST_PRICE)
FROM etc7
GROUP BY COUNTRY_NAME
ORDER BY AVG(PROD_LIST_PRICE) DESC

--6)
SELECT REGEXP_SUBSTR(CUST_EMAIL,'\w*.\w*$'), COUNT(*)
FROM Customers c
GROUP BY REGEXP_SUBSTR(CUST_EMAIL,'\w*.\w*$')

--7)
WITH
    etc1 AS ( -- PROD_ID для категории "Мужчины"
        SELECT PROD_ID
        FROM Products
        WHERE PROD_CATEGORY = 'Men'
        ),
    etc2 AS (
        SELECT CUST_ID, QUANTITY_SOLD
        FROM Sales
        WHERE PROD_ID IN (SELECT PROD_ID FROM etc1)
        ),
    etc3 AS (
        SELECT CUST_ID, COUNTRY_ID
        FROM Customers c
        ),
    etc4 AS (
        SELECT COUNTRY_ID, QUANTITY_SOLD
        FROM etc2
        JOIN etc3
        ON etc2.CUST_ID = etc3.CUST_ID
        ),
    etc5 AS (
        SELECT COUNTRY_ID, COUNTRY_NAME
        FROM Countries
        ),
    etc6 AS (
        SELECT COUNTRY_NAME, SUM(QUANTITY_SOLD) AS SUMQS
        FROM etc4
        JOIN etc5
        ON etc4.COUNTRY_ID = etc5.COUNTRY_ID
        GROUP BY COUNTRY_NAME
        )
SELECT *
FROM etc6
WHERE SUMQS > (SELECT AVG(SUMQS) FROM etc6)

--8)
WITH
    etc1 AS (
        SELECT COUNTRY_ID, COUNT(*) AS M_QUANTITY
        FROM Customers
        WHERE CUST_GENDER = 'M'
        GROUP BY COUNTRY_ID
        ),
    etc2 AS (
        SELECT COUNTRY_ID, COUNT(*) AS F_QUANTITY
        FROM Customers
        WHERE CUST_GENDER = 'F'
        GROUP BY COUNTRY_ID
        ),
    etc3 AS (
        SELECT etc1.COUNTRY_ID AS СТРАНА,
        ROUND(M_QUANTITY/(M_QUANTITY+F_QUANTITY)*100,1) AS  Процент_мужчин,
        ROUND(F_QUANTITY/(M_QUANTITY+F_QUANTITY)*100,1) AS  Процент_женщин
        FROM etc1
        JOIN etc2
        ON etc1.COUNTRY_ID = etc2.COUNTRY_ID
        ),
    etc4 AS (
        SELECT COUNTRY_ID, COUNTRY_NAME
        FROM COUNTRIES
        )
SELECT etc4.COUNTRY_NAME AS СТРАНА, Процент_мужчин AS "% мужчин", Процент_женщин  AS "% женщин"
FROM etc3
JOIN etc4
ON etc3.СТРАНА = etc4.COUNTRY_ID
ORDER BY etc4.COUNTRY_NAME

--9)
WITH
    etc1 AS (
        SELECT PROD_ID, TIME_ID,MAX(QUANTITY_SOLD) AS MAXSQ
        FROM SALES s2
        GROUP BY TIME_ID, PROD_ID
        ORDER BY PROD_ID
        ),
    etc2 AS (
        SELECT MAX(MAXSQ) AS MAXSQ2, PROD_NAME
        FROM etc1
        JOIN PRODUCTS p
        ON etc1.PROD_ID = p.PROD_ID
        GROUP BY PROD_NAME
        ORDER BY MAXSQ2 DESC
        )
SELECT MAXSQ2 AS "Макс покуп/день", PROD_NAME
FROM etc2
WHERE ROWNUM <=20

--10)
WITH
    etc1 AS (
        SELECT TIME_ID, PROD_SUBCATEGORY, MAX(QUANTITY_SOLD) AS MAXSQ
        FROM PRODUCTS p
        JOIN SALES s
        ON p.PROD_ID = s.PROD_ID
        GROUP BY TIME_ID, PROD_SUBCATEGORY
        ORDER BY PROD_SUBCATEGORY
        )
SELECT PROD_SUBCATEGORY, MAX(MAXSQ) AS MAXSQ
FROM etc1
GROUP BY  PROD_SUBCATEGORY
ORDER BY  MAXSQ DESC

--11)
CREATE TABLE "sales_User2_Rostislav_Teryaev" AS
WITH
    etc1 AS (
        SELECT DISTINCT TIME_ID, SUM(QUANTITY_SOLD) AS smqs,REGEXP_SUBSTR(TIME_ID,'\d{2}.\w*$') AS mnth
        FROM sales s
        GROUP BY REGEXP_SUBSTR(TIME_ID,'\d{2}.\w*$'), TIME_ID
        ),
    etc2 AS (
        SELECT MAX(smqs) AS maxsmqs
        FROM etc1
        ),
    etc3 AS (
        SELECT mnth
        FROM etc1
        WHERE etc1.smqs IN (SELECT maxsmqs FROM etc2)
        ),
    etc4 AS (
        SELECT TIME_ID
        FROM etc1
        WHERE etc1.mnth IN (SELECT mnth FROM etc3)
        )
SELECT *
FROM Sales s
WHERE s.TIME_ID IN (SELECT TIME_ID FROM etc4)


SELECT *
FROM "sales_User2_Rostislav_Teryaev"

--14)
DROP TABLE "sales_User2_Rostislav_Teryaev"