By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products
(`id` int, `name` varchar(9))
;
INSERT INTO products
(`id`, `name`)
VALUES
(1, 'asd wef'),
(2, 'gggg2222'),
(3, 'pppga 99'),
(4, 'lalala 55')
;
CREATE TABLE categories
(`id` int, `level` int, `parent_id` int, `name` varchar(7))
;
INSERT INTO categories
(`id`, `level`, `parent_id`, `name`)
VALUES
(20, 1, NULL, 'Fashion'),
(22, 2, 20, 'Top'),
(23, 3, 22, 'T-Shirt'),
(24, 3, 22, 'Jacket')
;
CREATE TABLE product_categories
(`product_id` int, `category_id` int)
;
INSERT INTO product_categories
(`product_id`, `category_id`)
VALUES
WITH prods AS (
SELECT product_id, MAX(category_id) AS stop_cat
FROM product_categories
GROUP BY product_id
)
SELECT c.id AS category_id,
COUNT(DISTINCT pc.product_id) AS total_product,
SUM(c.id = p.stop_cat) AS stopped_product
FROM categories c
JOIN product_categories pc ON pc.category_id = c.id
JOIN prods p ON p.product_id = pc.product_id
GROUP BY c.id
| category_id | total_product | stopped_product |
|---|---|---|
| 20 | 4 | 2 |
| 22 | 2 | 1 |
| 23 | 1 | 1 |