How to show count of products with category using SQL, Sql Server

Language: Layout:

DECLARE @Categories AS TABLE
	(
		category_id int, 
		category_name varchar(50),   
		sub_category_id int
	)
	INSERT INTO @Categories (category_id, category_name, sub_category_id) VALUES
	(1, 'Category_1'        , 0),
	(2, 'Category_2'        , 0),
	(3, 'Sub_Category_1'    , 1),
	(4, 'Sub_Category_2'    , 2),
	(5, 'Sub_Category_1_Sub', 3),
	(6, 'Sub_Category_2_Sub', 4);

DECLARE @Products AS TABLE
	(
		product_id  int,
		category_id int
	)

INSERT INTO @Products (product_id, category_id) VALUES
	(1, 3), -- Category_1
	(2, 4), -- Category_2
	(3, 3), -- Category_1
	(4, 4), -- Category_2
	(5, 5), -- Category_1
	(6, 5); -- Category_1

WITH CTE AS
	(
		SELECT category_id, category_name, sub_category_id, category_name As MainCategoryName
		FROM @Categories 
		WHERE sub_category_id = 0
		-- If you want to start from a particular subCategory you can change the where condition:
		-- for instance, `where category_id = 3` will count the products the belongs to Sub_Category_1 and Sub_Category_1_Sub
		UNION ALL

SELECT T.category_id, T.category_name, T.sub_category_id, MainCategoryName
		FROM @Categories AS T
		JOIN CTE
			ON T.sub_category_id = CTE.category_id
	)

SELECT MainCategoryName As CategoryName, COUNT(P.product_id ) As NumberOfProducts
	FROM CTE
	LEFT JOIN @Products As P
		ON P.category_id = CTE.category_id 
	GROUP BY MainCategoryName

View schema
Execution time: 0,03 sec, rows selected: 2, rows affected: 12, absolute service time: 0,22 sec, absolute service time: 0,22 sec