SELECT '---Задача 1---';
/*Задача 1
Задана таблица Empl с полями
    id (id сотрудника),
    start_time (дата начала работы сотрудника),
    final_time (дата увольнения сотрудника).

Нужно написать SQL-запрос, который выведет список сотрудников, которые уволились в этом месяце.*/

--создаем таблицу
CREATE TABLE Empl (
id int, 
start_time date, 
final_time date
);

--заполняем таблицу
INSERT INTO Empl
VALUES (1, '2023-04-23', '2024-04-05'),
       (2, '2022-08-18', '2024-05-01'),
       (3, '2021-06-28', '2023-04-11'),
       (3, '2021-07-21', '2024-02-05');

/* Текущий месяц - апрель (или май на момент проверки), текущий год - 2024.
Результатом запроса должна стать запись 1 (если текущий месяц апрель)
или запись 2 если текущий месяц май*/

--решение
SELECT id
FROM Empl
WHERE EXTRACT(MONTH FROM final_time) = EXTRACT(MONTH FROM CURRENT_DATE)
  AND EXTRACT(YEAR FROM final_time) = EXTRACT(YEAR FROM CURRENT_DATE);





SELECT '---Задача 2---';
/*Задача 2
Задана таблица Marks с полями
    student (имя ученика) и mark (оценка ученика).

Нужно написать SQL-запрос, который выведет список студентов,
чьи оценки не меньше, чем средние оценки класса.*/

--создаем таблицу
CREATE TABLE Marks (
student varchar(50),
mark int
);

--заполняем таблицу
INSERT INTO Marks
VALUES ('Оля', 3),
       ('Ира', 4),
       ('Катя', 5),
       ('Настя', 3),
       ('Аня', 3),
       ('Юля', 5),
       ('Маша', 5),
       ('Даша', 4);

/* средний бал студентов - 4, следовательно результатом запроса должны стать пять имен:
Ира, Катя, Юля, Маша, Даша - их оценки не меньше среднего бала, т.е. либо больше, либо равны 4 */

--решение
SELECT student
FROM Marks
WHERE mark >=
    (SELECT AVG(mark)
     FROM Marks);





SELECT '---Задача 3---';
/*Задача 3
Задана таблица OrderDet с полями
    manager (имя продавца),
    product (название проданного товара),
    quantity (количество проданного товара),
    dat (дата продажи).

Нужно написать SQL-запрос, который выведет список продавцов,
которые продали в 2017 году более 30 штук товара 'мониторы'*/

--создаем таблицу
CREATE TABLE OrderDet (
manager varchar(50),
product varchar(50),
quantity int, 
dat date
);

--заполняем таблицу
INSERT INTO OrderDet
VALUES ('Иван', 'мониторы', 29, '2017-07-21'),
       ('Иван', 'мониторы', 3, '2017-07-22'),
       ('Олег', 'мониторы', 35, '2018-03-14'),
       ('Петр', 'ноутбуки', 33, '2017-01-16'),
       ('Иван', 'наушники', 45, '2017-09-15'),
       ('Никита', 'мониторы', 14, '2017-05-03'),
       ('Никита', 'мониторы', 17, '2016-04-01');

/* Больше 30 мониторов в 2017 году продал только Иван, его запрос и должен вернуть.
Олег продал больше 30 мониторов в 2018 году,
Петр продал больше 30, но наушников,
а Никита продал больше 30 мониторов за два года*/

--решение
SELECT manager
FROM OrderDet
WHERE EXTRACT(YEAR FROM dat) = 2017
  AND product = 'мониторы'
GROUP BY manager
HAVING SUM(quantity) > 30;





SELECT '---Задача 4---';
/*Задача 4
Заданы 2 таблицы:
Department с полями dep (название отдела) и id (номер отдела);
Managers с полями manager (имя сотрудника), dep (номер отдела) и salary (зарплата сотрудника).
Примечание: некоторые отделы могут не иметь сотрудников.

Нужно написать SQL-запрос, который выведет список названий каждого отдела 
с максимальной заработной платой его сотрудников (если в отделе нет сотрудников, 
то вывести в поле MAX(salary) NULL).*/

--создаем таблицу Department
CREATE TABLE Department (
dep varchar(50),
id int
);

--заполняем таблицу Department
INSERT INTO Department
VALUES ('Отдел 1', 1),
       ('Отдел 2', 2),
       ('Отдел 3', 3),
       ('Отдел 4', 4),
       ('Отдел 5', 5);
       
--создаем таблицу Managers
CREATE TABLE Managers (
manager varchar(50),
dep int,
salary bigint
);

--заполняем таблицу Managers
INSERT INTO Managers
VALUES ('Оля', 1, 100000),
       ('Маша', 1, 170000),
       ('Даша', 1, 210000),
       ('Настя', 2, 190000),
       ('Ира', 2, 160000),
       ('Лена', 3, 90000),
       ('Света', 3, 95000),
       ('Аня', 5, 180000),
       ('Юля', 5, 185000);

/* Создано 5 отделов и 9 сотрудников
В первом отделе 3 сотрудника, максимальная зарплата 210 тыс,
во втором 2 сотрудника, максимальная зарплата 190 тыс,
в третьем 2 сотрудника, 190 тыс,
в четревртом сотрудников нет, запрос должен включить название отдела и отобразить пустоту (NULL),
в пятом 2 сотрудника, максимальная зарплата 185 тыс.
Эти данные и должен вывести запрос*/

--решение
SELECT d.dep, 
       MAX(m.salary) as max_salary
FROM Department AS d 
LEFT JOIN Managers AS m ON d.id = m.dep
GROUP BY d.dep;





SELECT '---Задача 5---';
/*Задача 5
Заданы 3 таблицы:

Orders с полями 
    cust_id (идентификатор покупателя), 
    prod_id (идентификатор товара), 
    price (стоимость проданного товара), 
    quantity (количество проданного товара), 
    dat (дата продажи);
    
Customers с полями FIO (ФИО покупателя) и id (идентификатор покупателя);
    
Products с полями product (название товара) и id (идентификатор товара).

Нужно написать SQL-запрос, который выведет список из ФИО каждого покупателя и названий 2 товаров, 
на которые этот покупатель потратил больше всего денег в прошлом году.*/

--создаем таблицу Orders
CREATE TABLE Orders (
cust_id int,
product_id int,
price bigint,
quantity bigint,
dat date
);

--заполняем таблицу Managers
INSERT INTO Orders
VALUES (1, 1, 100, 1, '2024-04-05'),
       (1, 2, 200, 2, '2023-05-01'),
       (1, 2, 300, 3, '2023-04-11'),
       (1, 3, 400, 1, '2023-02-05'),
       (2, 1, 100, 2, '2023-04-05'),
       (2, 1, 200, 3, '2023-05-01'),
       (2, 3, 300, 4, '2023-04-11'),
       (2, 3, 400, 5, '2023-04-05'),
       (2, 2, 100, 3, '2024-05-01'),
       (2, 1, 200, 2, '2023-04-11'),
       (3, 3, 300, 1, '2023-04-05'),
       (3, 2, 400, 2, '2024-05-01'),
       (3, 2, 400, 3, '2023-02-10'),
       (3, 1, 100, 7, '2023-03-17'),
       (1, 1, 200, 6, '2023-10-03'),
       (3, 1, 100, 3, '2023-04-11');

--создаем таблицу Customers
CREATE TABLE Customers (
FIO varchar(50),
id int
);

--заполняем таблицу Customers
INSERT INTO Customers
VALUES ('Иванов Иван Иванович', 1),
       ('Сергеев Сергей Сергеевич', 2),
       ('Петров Петр Петрович', 3);


--создаем таблицу Products
CREATE TABLE Products (
product varchar(50),
id int
);

--заполняем таблицу Products
INSERT INTO Products
VALUES ('Макароны', 1),
       ('Рис', 2),
       ('Гречка', 3);
       
--решение

/*Шаг 1. Сформируем обобщенное табличное выражение, в котором 
оставим только данные за прошлый год и создадим новое поле
с потраченной суммой на каждый товар каждым покупателем.*/
WITH orders_last_year AS(
SELECT cust_id,
       product_id,
       SUM(price * quantity) AS sum_pq
FROM Orders
WHERE EXTRACT(YEAR FROM dat) = EXTRACT(YEAR FROM CURRENT_DATE) - 1
GROUP BY cust_id,
         product_id),

/*Шаг 2. К полученному табличному выражению добавляем ранжирование
по убыванию потраченных денег на каждый товар по каждому покупателю.*/
orders_rank AS (
SELECT *,
       DENSE_RANK() OVER(PARTITION BY cust_id ORDER BY sum_pq DESC) AS rank
FROM orders_last_year
)

/*Шаг 3. Фильтруем данные, оставляя только первый и второй ранг,
джойним итоговую таблицу с таблицами Customers и Products,
обогощая ее ФИО и названием товара.*/
SELECT c.FIO,
       p.product
FROM orders_rank AS o
LEFT JOIN Customers AS c ON o.cust_id = c.id
LEFT JOIN Products AS p ON o.product_id = p.id
WHERE o.rank <= 2
ORDER BY c.FIO; --дополнительно упорядочим данные по ФИО 





SELECT '---Задача 6---';
/*Задача 6
Даны две таблицы: Friends (user_id, friend_id), оба поля ссылаются на пользователя соцсети. 
Likes (user_id, post_id) – посты, которые лайкнули пользователи соцсети.

Для некоторого пользователя user_id = 1 необходимо сформировать ленту рекомендаций, 
то есть список постов в порядке убывания популярности среди его друзей. 
Из рекомендаций исключить посты, которые пользователь уже лайкнул.
*/

--создаем таблицу Orders