--1
SELECT cust_first_name AS "First name",
    cust_last_name AS "Last name"
FROM sh.customers
WHERE cust_gender = 'F'
    AND cust_marital_status = 'married'
    AND cust_credit_limit IN(
        SELECT MIN(cust_credit_limit) FROM sh.customers)
    AND country_id NOT IN('JP', 'BR', 'IT')
;

--2
WITH customers_with_phone_end_77 AS (
    SELECT cust_first_name,
        cust_last_name,
        cust_city,
        cust_street_address,
        cust_main_phone_number,
        cust_email
    FROM sh.customers
    WHERE cust_main_phone_number LIKE '%77')
SELECT 'Name: ' || cust_first_name || ' ' || cust_last_name || '; ' ||
    'city: ' || cust_city || '; ' ||
    'address: ' || cust_street_address || '; ' ||
    'number: ' || cust_main_phone_number || '; ' ||
    'email: ' || cust_email || ';' AS "Querry"
FROM customers_with_phone_end_77
WHERE LENGTH(cust_street_address) IN(
    SELECT MAX(LENGTH(cust_street_address))
    FROM customers_with_phone_end_77)
;

--3
WITH min_price_girls AS (
        SELECT prod_id, prod_min_price
        FROM sh.products
        WHERE prod_category = 'Girls'),
    min_price_women AS (
        SELECT prod_id, prod_min_price
        FROM sh.products
        WHERE prod_category = 'Women')
SELECT *
FROM sh.customers
JOIN sh.sales USING (cust_id)
WHERE prod_id IN (
    SELECT prod_id
    FROM (
        SELECT prod_id
        FROM min_price_girls
        WHERE prod_min_price IN(
            SELECT MIN(prod_min_price)
            FROM min_price_girls)
        UNION
        SELECT prod_id
        FROM min_price_women
        WHERE prod_min_price IN(
            SELECT MIN(prod_min_price)
            FROM min_price_women)))
AND cust_year_of_birth > 1980
;

--4
SELECT *
FROM sh.customers customers_mens
WHERE cust_gender = 'M'
    AND cust_marital_status IS NULL
    AND cust_income_level LIKE 'D%'
    AND EXISTS (
        SELECT *
        FROM sh.customers
        WHERE country_id IN('DE', 'US')
            AND cust_id = customers_mens.cust_id)
;

--5
SELECT country_name AS "Coutry name",
    round(avg(amount_sold),2) AS "Average amount"
FROM sh.sales
JOIN sh.customers USING(cust_id)
JOIN sh.countries USING(country_id)
GROUP BY country_name
ORDER BY 2 DESC
;

--6
SELECT substr(cust_email, instr(cust_email, '@') + 1) AS Domen,
    COUNT(1) AS "Number of customers"
FROM sh.customers
GROUP BY substr(cust_email, instr(cust_email, '@') + 1)
;

--7
SELECT country_name,
    count_sold
FROM (
    SELECT country_name,
        COUNT(*) AS count_sold,
        round(avg(amount_sold), 2) AS avg_amount
    FROM sh.sales
    JOIN sh.products USING (prod_id)
    JOIN sh.customers USING (cust_id)
    JOIN sh.countries USING (country_id)
    WHERE prod_category = 'Men'
    GROUP BY country_name)
WHERE count_sold > avg_amount
;

--8
WITH quantity_cust_sex AS (
    SELECT * FROM
        (SELECT COUNT(cust_gender) AS sex_m,
            country_id
        FROM sh.customers
        WHERE cust_gender = 'M'
        GROUP BY country_id)
        JOIN
            (SELECT COUNT(cust_gender) AS sex_f,
                country_id
            FROM sh.customers
            WHERE cust_gender = 'F'
            GROUP BY country_id)
        USING(COUNTRY_ID))
SELECT country_name AS "Страна",
    round(sex_m * 100 / (sex_m + sex_f), 2) AS "% мужчин",
    round(sex_f * 100 / (sex_m + sex_f), 2) AS "% женщин"
FROM quantity_cust_sex
JOIN sh.countries USING (country_id)
ORDER BY country_name;

--9
WITH quantity_sales AS(
    SELECT MAX(quantity) AS max_quantity,
        prod_id
    FROM (
        SELECT prod_id,
            time_id,
            SUM(quantity_sold) AS quantity
        FROM sh.sales
        GROUP BY prod_id, time_id
        ORDER BY quantity DESC, prod_id ASC)
    GROUP BY prod_id
    ORDER BY 1 DESC)
SELECT max_quantity AS "Макс покуп/день",
    prod_name
FROM quantity_sales
JOIN sh.products USING (prod_id)
WHERE rownum <= 20
;

--10
SELECT MAX(quantity) AS max_quantity,
    prod_category
FROM (
    SELECT prod_category,
        time_id,
        SUM(quantity_sold) AS quantity
    FROM sh.sales
    JOIN sh.products USING (prod_id)
    GROUP BY prod_category, time_id)
GROUP BY prod_category
ORDER BY 1 DESC
;

--11
CREATE TABLE sales_user7_Mikhail_Avdeev
AS (SELECT *
    FROM sh.sales
    WHERE TRUNC(time_id, 'MM') IN(
        SELECT TRUNC(time_id, 'MM')
        FROM (
            SELECT time_id,
                SUM(quantity_sold) AS sum_quantity
            FROM sh.sales
            GROUP BY time_id)
        WHERE sum_quantity IN (
            SELECT MAX(sum_quantity)
            FROM (
            SELECT time_id,
                SUM(quantity_sold) AS sum_quantity
            FROM sh.sales
            GROUP BY time_id))
        )
    )
;

--12
UPDATE sales_user7_Mikhail_Avdeev
SET time_id = to_date(to_char(time_id, 'DD.MM.YY') || ' ' ||
    FLOOR(DBMS_RANDOM.VALUE (0, 24)) || ':' ||
    FLOOR(DBMS_RANDOM.VALUE (0, 60)) || ':' ||
    FLOOR(DBMS_RANDOM.VALUE (0, 60)) , 'DD.MM.YY HH24:MI:SS')
;
commit;

--13
SELECT to_char(TIME_ID, 'DD') AS "Day",
    to_char(TIME_ID, 'hh24') AS "Hour",
    SUM(quantity_sold)
FROM sales_user7_Mikhail_Avdeev
GROUP BY to_char(TIME_ID, 'DD'),
    to_char(TIME_ID, 'hh24')
ORDER BY 1, 2
;

--14
DROP TABLE sales_user7_Mikhail_Avdeev;