-- TASK 6

SELECT Сategories.name, Products.name, Products.in_stock, SUM(Products.price * Products.in_stock) -- (1)
OVER(PARTITION BY Сategories.name ORDER BY Products.name) AS Total_price FROM Products
JOIN Сategories ON Сategories.id = Products.category_id;


SELECT Products.name, Products.in_stock, AVG(Products.price)                                      -- (2)
OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Average_price FROM Products;


WITH Items(name, price, manufacturer, supplier) AS                                                -- (3)
(SELECT Products.name, Products.price, Manufacturers.name, Suppliers.name FROM Products
JOIN Manufacturers, Suppliers ON Manufacturers.id = Products.manufacturer_id AND Suppliers.id = Products.supplier_id)
SELECT name, manufacturer, supplier, price FROM Items WHERE price > 200;



WITH Providers_and_Producers(manufacturer_name, product_name, product_count, product_price) AS   -- (4)
  (
    SELECT Suppliers.name, Products.name, Products.price, Products.in_stock FROM Products
    JOIN Suppliers ON Suppliers.id = Products.supplier_id
    INTERSECT
    SELECT Manufacturers.name, Products.name, Products.price, Products.in_stock FROM Products
    JOIN Manufacturers ON Manufacturers.id = Products.manufacturer_id
  )
SELECT * FROM Providers_and_Producers;



CREATE TABLE IF NOT EXISTS Companies(                                                           -- (5)
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subdivision_name TEXT NOT NULL,
    parent_id INTEGER
);
INSERT INTO Companies(subdivision_name, parent_id) VALUES
('Department_IT', NULL),
('IT_managment', 1),
('Development_and_control_department', 1),
('Information_systems_department', 1),
('Server_hardware_department', 2),
('User_support_department', 2),
('Communications_department', 2),
('Quality_control_department', 3),
('Project_office', 3);

WITH RECURSIVE IT_hierarchy AS (
  SELECT Companies.id, Companies.parent_id, Companies.subdivision_name, 2 AS level
  FROM Companies WHERE Companies.subdivision_name = 'IT_managment'
  UNION ALL
  SELECT Companies.id, Companies.parent_id, Companies.subdivision_name, IT_hierarchy.level + 1 AS level FROM IT_hierarchy, Companies
  WHERE Companies.parent_id = IT_hierarchy.id
  )
SELECT subdivision_name FROM IT_hierarchy WHERE subdivision_name != 'IT_managment';