SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE warehouses ( "warehouse_id" INTEGER, "name" varchar(1000), "city" varchar(100), "date_open" DATE, "date_close" DATE ); INSERT INTO warehouses ("warehouse_id", "name", "city", "date_open", "date_close") VALUES ('1','name1','city1', '04-12-2019 21:04:25', NULL), ('2','name2','city2', '06-05-2018 21:04:25', NULL), ('3','name3','city3', '07-04-2019 21:04:25', NULL), ('4','name4','Moscow', '08-03-2018 21:04:25', NULL), ('5','name5','city5', '09-05-2018 21:04:25', '10-05-2021 21:04:25'), ('6','name6','Moscow', '08-25-2018 21:04:25', NULL); CREATE TABLE product ( "product_id" INTEGER, "name" varchar(1000), "group1" varchar(100), "group2" varchar(100), "group3" varchar(100), "weight" decimal(5, 2), "shelf_life" INTEGER ); INSERT INTO product ("product_id", "name", "group1", "group2", "group3", "weight", "shelf_life") VALUES ('1','name1','group_1_1', 'group_2_1', 'group_3_1', '2.2', '30'), ('2','name2 samokat','group_1_2', 'group_2_2', 'group_3_2', '3.0', '7'), ('3','name3','group_1_3', 'group_2_3', 'group_3_3', '1.5', '7'), ('4','name4 Samokat','group_1_4', 'group_2_4', 'group_3_4', '1.0', '5'), ('5','name5','group_1_5', 'group_2_5', 'group_3_5', '0.5', '3'); CREATE TABLE orders ( "order_id" INTEGER, "warehouse_id" INTEGER, "user_id" INTEGER, "date" DATETIME, "paid_amount" decimal(5, 2), "quantity" decimal(5, 2) ); INSERT INTO orders ("order_id", "warehouse_id", "user_id", "date", "paid_amount", "quantity") VALUES ('1','1','3', '05-10-2020 11:04:25', '120.0', '1.0'), ('2','3','5', '04-12-2021 11:04:25', '900.0', '3.0'), ('3','2','4', '06-07-2020 11:04:25', '500.0', '2.0'), ('4','1','2', '08-20-2020 11:04:25', '150.0', '1.0'), ('5','4','7', '05-15-2021 11:04:25', '250.0', '1.0'), ('6','6','9', '05-10-2021 11:04:25', '160.0', '2.0'), ('7','4','10', '09-09-2021 11:04:25', '300.0', '2.0'); CREATE TABLE lost ( "date" DATETIME, "warehouse_id" INTEGER, "product_id" INTEGER, "item_id" INTEGER, "quantity" decimal(8, 2), "amount" decimal(8, 2) ); INSERT INTO lost ("date", "warehouse_id", "product_id", "item_id", "quantity", "amount") VALUES ('05-15-2020 11:04:25', '1', '1', '1', '10.0', '1000.0'), ('04-12-2021 11:04:25', '2', '2', '2', '5.0', '500.0'), ('06-07-2020 11:04:25', '3', '2', '3', '3.0', '300.0'), ('08-20-2020 11:04:25', '2', '3', '4', '4.0', '600.0'), ('09-15-2021 11:04:25', '6', '5', '5', '7.0', '1500.0'), ('09-10-2021 11:04:25', '4', '4', '6', '5.0', '700.0'), ('09-09-2021 11:04:25', '3', '4', '7', '9.0', '2000.0'); CREATE TABLE order_line ( "order_id" INTEGER, "date" DATETIME, "warehouse_id" INTEGER, "product_id" INTEGER, "price" decimal(5, 2), "regular_price" decimal(5, 2), "cost_price" decimal(5, 2), "quantity" decimal(5, 2), "paid_amount" decimal(5, 2) ); INSERT INTO order_line ("order_id", "date", "warehouse_id", "product_id", "price", "regular_price", "cost_price", "quantity", "paid_amount") VALUES ('1','05-10-2020 11:04:25', '1', '1', '120.0', '120.0', '60.0', '1.0', '120.0'), ('2','04-12-2021 11:04:25', '3', '2', '300.0', '300.0', '150.0', '1.0', '300.0'), ('2','04-12-2021 11:04:25', '3', '2', '300.0', '300.0', '150.0', '1.0', '300.0'), ('2','04-12-2021 11:04:25', '3', '2', '300.0', '300.0', '150.0', '1.0', '300.0'), ('3','06-07-2020 11:04:25', '2', '4', '250.0', '250.0', '100.0', '1.0', '250.0'), ('3','06-07-2020 11:04:25', '2', '4', '250.0', '250.0', '100.0', '1.0', '250.0'), ('4','08-20-2020 11:04:25', '1', '3', '150.0', '150.0', '80.0', '1.0', '150.0'), ('5','05-15-2021 11:04:25', '4', '4', '250.0', '250.0', '100.0', '1.0', '250.0'), ('6','05-10-2021 11:04:25', '6', '5', '80.0', '80.0', '40.0', '1.0', '80.0'), ('6','05-10-2021 11:04:25', '6', '5', '80.0', '80.0', '40.0', '1.0', '80.0'), ('7','09-09-2021 11:04:25', '4', '3', '150.0', '150.0', '80.0', '1.0', '150.0'), ('7','09-09-2021 11:04:25', '4', '3', '150.0', '150.0', '80.0', '1.0', '150.0'); SELECT TOP 10 p.group1, p.name, SUM(ol.paid_amount) AS paid_amount, COUNT(DISTINCT ol.warehouse_id) as count_w, SUM(ol.paid_amount) / COUNT(DISTINCT ol.warehouse_id) AS avg_paid_amount FROM order_line AS ol INNER JOIN product as p ON ol.product_id = p.product_id INNER JOIN warehouses as w ON ol.warehouse_id = w.warehouse_id WHERE CAST(ol.date as date) BETWEEN '2021-05-01' AND '2021-05-31' AND w.city = 'Moscow' GROUP BY p.group1, p.name ORDER BY avg_paid_amount DESC