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

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

WITH Items(name, price, manufacturer, provider) AS
        (SELECT Products.name, Products.price, Producers.name, Providers.name FROM Products
        JOIN Producers, Providers ON Producers.id = Products.producer_id AND Providers.id = Products.provider_id
   ) SELECT name, manufacturer, provider, price FROM Items WHERE price > 200;

INSERT INTO Providers(name, contract_amount) VALUES
('BestЗавод', 100000);

INSERT INTO Products(name, provider_id, producer_id, category_id, price, weight_volume, in_stock) VALUES
('Кефир', 6, 1, 4, 125, 1, 77);


WITH Providers_and_Producers(manufacturer_name, product_name, product_count, product_price) AS
  (
    SELECT Providers.name, Products.name, Products.price, Products.in_stock FROM Products
    JOIN Providers ON Providers.id = Products.provider_id
    INTERSECT
    SELECT Producers.name, Products.name, Products.price, Products.in_stock FROM Products
    JOIN Producers ON Producers.id = Products.producer_id
  )
SELECT * FROM Providers_and_Producers;

CREATE TABLE IF NOT EXISTS Companies(
    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';