use mytools;

DROP TABLE IF EXISTS sold_items;
CREATE TABLE sold_items (region CHAR(20), qty INTEGER, sold_price DECIMAL(8,2), sold_date DATE);

INSERT INTO sold_items VALUES ('branch1', 1, 15.00,'2021-12-03');
INSERT INTO sold_items VALUES ('branch1', 2, 10.00,'2021-12-03');
INSERT INTO sold_items VALUES ('branch1', 3, 10.00,'2021-12-03');

INSERT INTO sold_items VALUES ('branch2', 3, 10.00,'2021-12-01');
INSERT INTO sold_items VALUES ('branch2', 2, 10.00,'2021-12-01');
INSERT INTO sold_items VALUES ('branch2', 1, 10.00,'2021-12-01');

INSERT INTO sold_items VALUES ('branch1', 5, 10.00,'2021-11-15');
INSERT INTO sold_items VALUES ('branch1', 6, 10.00,'2021-11-15');
INSERT INTO sold_items VALUES ('branch1', 8, 15.00,'2021-11-15');

INSERT INTO sold_items VALUES ('branch1', 5, 15.00,'2021-11-10');
INSERT INTO sold_items VALUES ('branch2', 5, 10.00,'2021-11-10');
INSERT INTO sold_items VALUES ('branch3', 5, 10.00,'2021-11-10');

INSERT INTO sold_items VALUES ('branch1', 5, 15.00,'2021-11-05');
INSERT INTO sold_items VALUES ('branch2', 6, 10.00,'2021-11-05');
INSERT INTO sold_items VALUES ('branch3', 8, 10.00,'2021-11-05');

-- sold_price-based list:
SELECT sold_price
FROM sold_items
ORDER BY sold_price;

-- list based on region and sold_price:
SELECT region, sum(sold_price)
FROM sold_items
GROUP BY region
ORDER BY region, sum(sold_price) DESC;

-- list based on region and revenue:
SELECT region, sum(sold_price*qty)
FROM sold_items
GROUP BY region
ORDER BY region, sum(sold_price*qty) DESC;

-- list based on revenue and region:
SELECT sum(sold_price*qty), region
FROM sold_items
GROUP BY region
ORDER BY sum(sold_price*qty) DESC, region;

-- list based on date, revenue, and region:
SELECT sold_date, sum(sold_price*qty), region
FROM sold_items
GROUP BY sold_date, region
ORDER BY sold_date, sum(sold_price*qty) DESC, region;

