with h_ave as(
select AGE,
GENDER_CODE,
avg(AVERAGE_VALUE) as h,
count(1) as N
from  SCHOOL_HEALTH
where CATEGORY_CODE = "10" and SURVEY_YEAR = 2019
group by 1,2
),

w_ave as(
select AGE,
GENDER_CODE,
avg(AVERAGE_VALUE) as w,
count(1) as N
from  SCHOOL_HEALTH
where CATEGORY_CODE = "20"  and SURVEY_YEAR = 2019
group by 1,2
),

htable as(
select 
PF_CODE,
s.GENDER_CODE,
s.AGE,
AVERAGE_VALUE,
h,
N
from SCHOOL_HEALTH s left join h_ave on (s.AGE = h_ave.AGE and s.GENDER_CODE = h_ave.GENDER_CODE)
where CATEGORY_CODE = "10" and SURVEY_YEAR = 2019
group by 1,2
),

wtable as(
select 
PF_CODE,
s.GENDER_CODE,
s.AGE,
AVERAGE_VALUE,
w,N
from SCHOOL_HEALTH s left join w_ave on (s.AGE = w_ave.AGE and s.GENDER_CODE = w_ave.GENDER_CODE)
where CATEGORY_CODE = "20" and SURVEY_YEAR = 2019
),
hratio as(
select GENDER_CODE,AGE,
round(sum(
case 
when round(h,1) >= AVERAGE_VALUE then 1.0/N
else 0
end
)*100,1) as ratio
from htable
group by 1,2
),
wratio as(
select GENDER_CODE,AGE,
round(sum(
case 
when round(w,1) >= AVERAGE_VALUE then 1.0/N
else 0
end
)*100,1) as ratio
from wtable
group by 1,2
)
select * from hratio