MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Orders (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
order_number VARCHAR(255),
product_id INT,
Qty INT);

CREATE TABLE Products (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
UnitPrice DECIMAL(9,2));

INSERT INTO Products VALUES
(1,"product1",0.25),
(2,"product2",0.33),
(3,"product3",0.5),
(4,"product4",1),
(5,"product5",0.62),
(6,"product6",1),
(7,"product7",1.17),
(8,"product8",1),
(9,"product9",1.5),
(10,"product10",1.25),
(11,"product11",2.75),
(12,"product12",6),
(13,"product13",2.17),
(14,"product14",7),
(15,"product15",1.88),
(16,"product16",2.67),
(17,"product17",4.25),
(18,"product18",2.25),
(19,"product19",9.5),
(20,"product20",2.5);


Records: 20  Duplicates: 0  Warnings: 0


INSERT INTO Orders(order_number, product_id, Qty)
VALUES 
('ORD0001', 1, 10), ('ORD0001', 2, 1), ('ORD0001', 3, 5), ('ORD0001', 4, 7),
('ORD0002', 1, 9),('ORD0002', 3, 2),('ORD0002', 4, 2),('ORD0003', 2, 3),
('ORD0003', 4, 4),('ORD0004', 1, 10),('ORD0004', 2, 11),('ORD0004', 3, 1),
('ORD0004', 4, 1),('ORD0004', 5, 1),('ORD0005', 5, 2),('ORD0005', 6, 8),
('ORD0006', 7, 2),('ORD0006', 8, 2),('ORD0006', 10, 5),('ORD0007', 9, 4),
('ORD0007', 11, 3),('ORD0008', 12, 13),('ORD0008', 19, 10),('ORD0008', 20, 10),
('ORD0009', 1, 10), ('ORD0009', 2, 1), ('ORD0010', 13, 5), ('ORD0010', 14, 7),
('ORD0011', 1, 9),('ORD0012', 3, 2),('ORD0012', 4, 2),('ORD0013', 2, 3),
('ORD0013', 4, 4),('ORD0014', 1, 10),('ORD0014', 2, 11),('ORD0014', 3, 1),
('ORD0014', 4, 1),('ORD0014', 15, 1),('ORD0015', 15, 2),('ORD0015', 16, 8),
('ORD0016', 17, 2),('ORD0016', 18, 2),('ORD0017', 10, 5),('ORD0017', 19, 4),
('ORD0018', 11, 3),('ORD0018', 12, 13),('ORD0019', 19, 10),('ORD0020', 20, 10);

Records: 48  Duplicates: 0  Warnings: 0


SELECT P.* 
FROM Products P 
 JOIN ( SELECT product_id
         FROM Orders
        GROUP BY product_id
        ORDER BY SUM(Qty) DESC
    LIMIT 10) O 
ON P.id=O.product_id;


id
product_name
UnitPrice


1
product1
0.25

2
product2
0.33

12
product12
6.00

19
product19
9.50

4
product4
1.00

20
product20
2.50

3
product3
0.50

10
product10
1.25

6
product6
1.00

16
product16
2.67


SELECT P.*
FROM Orders O 
 JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(Qty) DESC
LIMIT 10;


id
product_name
UnitPrice


1
product1
0.25

2
product2
0.33

12
product12
6.00

19
product19
9.50

4
product4
1.00

20
product20
2.50

3
product3
0.50

10
product10
1.25

6
product6
1.00

16
product16
2.67


SELECT P.*
FROM Orders O 
 JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(UnitPrice*Qty) DESC
LIMIT 10;


id
product_name
UnitPrice


19
product19
9.50

12
product12
6.00

20
product20
2.50

14
product14
7.00

16
product16
2.67

4
product4
1.00

11
product11
2.75

1
product1
0.25

10
product10
1.25

13
product13
2.17
id	product_name	UnitPrice
1	product1	0.25
2	product2	0.33
12	product12	6.00
19	product19	9.50
4	product4	1.00
20	product20	2.50
3	product3	0.50
10	product10	1.25
6	product6	1.00
16	product16	2.67