Calculate products count based on category tree table
Use a recursive CTE:

WITH RECURSIVE cats AS (
   SELECT 1::bigint AS id
 UNION
   SELECT c.id
   FROM category AS c
      JOIN cats ON c.parentid = cats.id
)
SELECT count(*)
FROM product
   JOIN cats ON cats.id = product.categoryid;


This query uses category id number 1 as an example. Replace it with the appropriate category id or use a parameter instead.