with ave as (
select 
AGE,GENDER_CODE,
round(avg(case when CATEGORY_CODE = '10' then AVERAGE_VALUE else null end ),1) as H_AVG,
round(avg(case when CATEGORY_CODE = '20' then AVERAGE_VALUE else null end ),1) as W_AVG
from SCHOOL_HEALTH
where SURVEY_YEAR = 2019
group by AGE,GENDER_CODE
)
select AGE, case GENDER_CODE when '20' then 'MALE' else 'FEMALE' end as GENDER,
H_AVG,
round(
cast((select count(1) from SCHOOL_HEALTH 
where age = ave.age 
and   gender_code = ave.gender_code
and  CATEGORY_CODE = '10'
and  SURVEY_YEAR = 2019
and  AVERAGE_VALUE >= ave.H_AVG ) as real) * 100
/ (
select count(1) 
from SCHOOL_HEALTH 
where age = ave.age 
and gender_code = ave.gender_code
and  SURVEY_YEAR = 2019
and  CATEGORY_CODE = '10'), 1) || '%' as H_PER,
W_AVG,
round(
cast((select count(1) from SCHOOL_HEALTH 
where age = ave.age 
and   gender_code = ave.gender_code
and  CATEGORY_CODE = '20'
and  SURVEY_YEAR = 2019
and  AVERAGE_VALUE >= ave.W_AVG ) as real) * 100
/ (
select count(1) 
from SCHOOL_HEALTH 
where age = ave.age 
and gender_code = ave.gender_code
and  SURVEY_YEAR = 2019
and  CATEGORY_CODE = '20'), 1) || '%' as W_PER
from ave
order by AGE desc, GENDER_CODE desc
;