With GROUPBY function the following result is generated
GROUPBY
=GROUPBY(B1:B7,C1:D7,LAMBDA(a,SUM(IF(a<>"",1,0))/ROWS(a)),3,0)