MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE products (
    product_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name varchar(100),
    PRIMARY KEY (product_id)
);

CREATE TABLE orders (
    order_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    product_id MEDIUMINT NOT NULL,
    confirmed_on TIMESTAMP NULL,
    fulfilled_on TIMESTAMP NULL,
    PRIMARY KEY (order_id)
);

CREATE TABLE orders_products (
    order_id MEDIUMINT NOT NULL,
    product_id MEDIUMINT NOT NULL
);
INSERT INTO products (name)
VALUES
('Product 1'),
('Product 2'),
('Product 3'),
('Product 4'),
('Product 5'),
('Product 6'),
('Product 7'),
('Product 8'),
('Product 9'),
('Product 10');

INSERT INTO orders (product_id, fulfilled_on, confirmed_on)
VALUES
(1,NOW(),NOW()),
(2,NULL,NOW()),
(3,NULL,NULL),
(4,NULL,NOW()),
(5,NULL,NULL),
(5,NULL,NULL),
(6,NULL,NOW()),
(7,NULL,NOW()),
(8,NULL,NULL),
(9,NOW(),NOW()),
(10,NOW(),NOW());

INSERT INTO orders_products (product_id, order_id)
VALUES
(1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(5,5),
(6,6),
(7,7),
(8,8),
(9,9),
(10,10);
SELECT * FROM products


product_id
name


1
Product 1

2
Product 2

3
Product 3

4
Product 4

5
Product 5

6
Product 6

7
Product 7

8
Product 8

9
Product 9

10
Product 10


SELECT * FROM orders


order_id
product_id
confirmed_on
fulfilled_on


1
1
2022-08-25 22:10:58
2022-08-25 22:10:58

2
2
2022-08-25 22:10:58
null

3
3
null
null

4
4
2022-08-25 22:10:58
null

5
5
null
null

6
5
null
null

7
6
2022-08-25 22:10:58
null

8
7
2022-08-25 22:10:58
null

9
8
null
null

10
9
2022-08-25 22:10:58
2022-08-25 22:10:58

11
10
2022-08-25 22:10:58
2022-08-25 22:10:58


SELECT * FROM orders_products


order_id
product_id


1
1

2
2

3
3

4
4

5
5

5
5

6
6

7
7

8
8

9
9

10
10


SELECT 
    p.product_id,
    p.name,
    @confirmed_nb := COUNT(DISTINCT IF(o.confirmed_on IS NOT NULL AND o.fulfilled_on IS NULL, o.order_id, NULL)) AS confirmed_nb,
    @fulfilled_nb := COUNT(DISTINCT IF(o.fulfilled_on IS NOT NULL, o.order_id, NULL)) AS fulfilled_nb,
    IF(COUNT(DISTINCT IF(o.confirmed_on IS NOT NULL AND o.fulfilled_on IS NULL, o.order_id, NULL)) > 0, 'POPUPLAR', 'UNPOPULAR') AS popularity,
    @confirmed_nb AS confirmed_nb_from_var
FROM products p
    JOIN orders_products op ON op.product_id = p.product_id
    JOIN orders o ON o.order_id = op.order_id
    GROUP BY p.product_id


product_id
name
confirmed_nb
fulfilled_nb
popularity
confirmed_nb_from_var


1
Product 1
0
1
UNPOPULAR
null

2
Product 2
1
0
POPUPLAR
null

3
Product 3
0
0
UNPOPULAR
null

4
Product 4
1
0
POPUPLAR
null

5
Product 5
0
0
UNPOPULAR
null

6
Product 6
0
0
UNPOPULAR
null

7
Product 7
1
0
POPUPLAR
null

8
Product 8
1
0
POPUPLAR
null

9
Product 9
0
0
UNPOPULAR
null

10
Product 10
0
1
UNPOPULAR
null

product_id	name
1	Product 1
2	Product 2
3	Product 3
4	Product 4
5	Product 5
6	Product 6
7	Product 7
8	Product 8
9	Product 9
10	Product 10

order_id	product_id	confirmed_on	fulfilled_on
1	1	2022-08-25 22:10:58	2022-08-25 22:10:58
2	2	2022-08-25 22:10:58	null
3	3	null	null
4	4	2022-08-25 22:10:58	null
5	5	null	null
6	5	null	null
7	6	2022-08-25 22:10:58	null
8	7	2022-08-25 22:10:58	null
9	8	null	null
10	9	2022-08-25 22:10:58	2022-08-25 22:10:58
11	10	2022-08-25 22:10:58	2022-08-25 22:10:58

product_id	name	confirmed_nb	fulfilled_nb	popularity	confirmed_nb_from_var
1	Product 1	0	1	UNPOPULAR	null
2	Product 2	1	0	POPUPLAR	null
3	Product 3	0	0	UNPOPULAR	null
4	Product 4	1	0	POPUPLAR	null
5	Product 5	0	0	UNPOPULAR	null
6	Product 6	0	0	UNPOPULAR	null
7	Product 7	1	0	POPUPLAR	null
8	Product 8	1	0	POPUPLAR	null
9	Product 9	0	0	UNPOPULAR	null
10	Product 10	0	1	UNPOPULAR	null