;
WITH ChildrenCTE AS (
  SELECT top 100 percent RootID = Product_ID, Product_ID,Parent_ID
  FROM    Product_Tree
  WHERE Parent_ID=0
  order by logicOrder          
  UNION ALL
  SELECT top 100 percent cte.RootID, d.Product_ID,d.Parent_ID
  FROM    ChildrenCTE cte
          INNER JOIN Product_Tree d ON d.Parent_ID = cte.Product_ID
 order by d.logicOrder          
)
SELECT  d.RowId, d.Product_ID, d.Parent_ID, ISNULL(cnt.Children,0)
FROM Product_Tree d
        LEFT OUTER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.Product_ID
    order by d.Product_ID

1
  2
    3
  4
  5
    6
    7
      8

ID  COUNT
1   4
2   1
3   0
4   0
5   2
6   0
7   1
8   0

select * from tree where id not in (select distinct parent_id from tree where parent_id is not null)

declare @TT table (product_id int,Parent_id int)
insert into @TT(Product_ID,Parent_ID) values(1,  0)
insert into @TT(Product_ID,Parent_ID) values(2,  1)
insert into @TT(Product_ID,Parent_ID) values(3,  2)
insert into @TT(Product_ID,Parent_ID) values(4,  1)
insert into @TT(Product_ID,Parent_ID) values(5,  1)
insert into @TT(Product_ID,Parent_ID) values(6,  5)
insert into @TT(Product_ID,Parent_ID) values(7,  5)
insert into @TT(Product_ID,Parent_ID) values(8,  7);
;with cteRecurs(AncestorId,Product_ID,Parent_Id)
as
(   Select AncestorId=Product_ID,Product_ID,Parent_Id
    from @TT T
    union all
    Select cteRecurs.AncestorId,T.Product_ID,T.Parent_Id
      from cteRecurs,           @TT T
     where T.Parent_Id = cteRecurs.Product_ID 
 )
 select cc.AncestorId, count(*)-1
	from cteRecurs cc
	group by cc.AncestorId
OPTION  (MAXRECURSION 0) ;

1	7
2	1
3	0
4	0
5	3
6	0
7	1
8	0

with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
),
r as
(
	select
		0 as lvl,
		id as l0,
		null as l1,
		null as l2,
		null as l3,
		null as l4,
		null as l5,
		null as l6,
		null as l7,
		null as l8,
		id,
		id_p,
		iif(id_p = 0, 1, 0) as is_root
	from 
		data d1
	where
		not exists(select * from data d2 where d1.id = d2.id_p)

	union all

	select
		r.lvl + 1 as lvl,
		r.l0 as l0,
		iif(lvl + 1 = 1, d.id, r.l1) as l1,
		iif(lvl + 1 = 2, d.id, r.l2) as l2,
		iif(lvl + 1 = 3, d.id, r.l3) as l3,
		iif(lvl + 1 = 4, d.id, r.l4) as l4,
		iif(lvl + 1 = 5, d.id, r.l5) as l5,
		iif(lvl + 1 = 6, d.id, r.l6) as l6,
		iif(lvl + 1 = 7, d.id, r.l7) as l7,
		iif(lvl + 1 = 8, d.id, r.l8) as l8,
		d.id,
		d.id_p,
		iif(d.id_p = 0, 1, 0) as is_root
	from
		data d
			inner join 
		r on d.id = r.id_p
)
select
	t.id, sum(iif(r.l0 <> t.id, 1, 0))cnt
from 
	r
		cross apply
	(values (l0), (l1), (l2), (l3), (l4), (l5), (l6), (l7), (l8)) as t(id)
where
	t.id is not null
	and r.is_root = 1
group by
	t.id

id          cnt
----------- -----------
1           4
2           1
3           0
4           0
5           2
6           0
7           1
8           0

with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
),
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
rt(n) as (select row_number() over(order by (select 0)) from l4 t1, l4 t2),
r as
(
	select
		0 as lvl,
		cast(id as varbinary(max)) as p,
		id,
		id_p,
		iif(id_p = 0, 1, 0) as is_root
	from 
		data d1
	where
		not exists(select * from data d2 where d1.id = d2.id_p)

	union all

	select
		r.lvl + 1 as lvl,
		p + cast(d.id as varbinary(max)) as p,
		d.id,
		d.id_p,
		iif(d.id_p = 0, 1, 0) as is_root
	from
		data d
			inner join 
		r on d.id = r.id_p
)
select
	t.id, sum(iif(t.id_c <> t.id, 1, 0)) as cnt
from 
	r
		cross apply
	(
		select top(datalength(r.p) / 4)
			convert(int, cast(substring(r.p, 4 * (n - 1) + 1, 4) as varbinary(4))) as id,
			convert(int, cast(left(r.p, 4) as varbinary(4))) as id_c
		from
			rt
	) as t(id, id_c)
where
	t.id is not null
	and r.is_root = 1
group by
	t.id

with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
), cte as
(
	select d1.id, d1.id as id_p 
	from data d1
		left  join data d2
			on d1.id = d2.id_p
	where d2.id is null
	union all
	select cte.id, d1.id_p
	from	cte 
		inner join data d1
			on cte.id_p = d1.id
 )
 select id_p, count(case when id = id_p then null else id end) from cte
 where id_p != 0
 group by id_p
 order by id_p

with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
),
s as
(
 select
  d.id, d.id_p, d.id as id_l, 1 as is_leaf
 from
  data d
 where
  not exists(select 1 from data where id_p = d.id)

 union all

 select
  d.id, d.id_p, s.id_l, 0
 from
  s join
  data d on d.id = s.id_p
)
select
 id, count(distinct case when is_leaf = 0 then id_l end)
from
 s
group by
 id;

declare @TT table (product_id int,Parent_id int)
insert into @TT(Product_ID,Parent_ID) values
  (1,  0),(2,  1),(3,  2),(4,  1),(5,  1),(6,  5),(7,  5),(8,  7);

;with rec as (
	select t.product_id, null middle, t.product_id nxt
    from @TT t
  union all
  select r.product_id, r.nxt, t.product_id
    from rec r
    join @TT t on t.Parent_id=r.nxt
  )
select product_id, count(distinct middle)qty
from rec 
group by product_id

declare @TT table (product_id int,Parent_id int)
insert into @TT(Product_ID,Parent_ID) values
  (1,  0),(2,  1),(3,  2),(4,  1),(5,  1),(6,  5),(7,  5),(8,  7), (7, 4);

;with rec as (
	select t.product_id, null middle, t.product_id nxt
    from @TT t
  union all
  select r.product_id, isnull(nullif(r.nxt,r.product_id),t.product_id), t.product_id
    from rec r
    join @TT t on t.Parent_id=r.nxt
  )
select product_id, count(distinct middle)qty from rec group by product_id