with tmp as(
select PF_CODE,CATEGORY_CODE,GENDER_CODE,AGE,AVERAGE_VALUE,
ROUND(avg(AVERAGE_VALUE)
      over(partition by CATEGORY_CODE,GENDER_CODE,AGE)
      ,1) as win_avg
  from SCHOOL_HEALTH
 where SURVEY_YEAR = 2019),
tmp2 as(
select AGE,CATEGORY_CODE,GENDER_CODE,
avg(AVERAGE_VALUE) as avg1,
cast(
sum(case when AVERAGE_VALUE >= win_avg
         then 1 else 0 end)
as real) / count(*) as avg2
  from tmp
group by AGE,CATEGORY_CODE,GENDER_CODE),
tmp3 as(
select AGE,GENDER_CODE,
case GENDER_CODE when 20 then 'MALE'
                 when 30 then 'FEMALE' end as GENDER,
max(case CATEGORY_CODE when 10 then avg1 end) as H_AVG,
max(case CATEGORY_CODE when 10 then avg2 end) as H_PER,
max(case CATEGORY_CODE when 20 then avg1 end) as W_AVG,
max(case CATEGORY_CODE when 20 then avg2 end) as W_PER
  from tmp2
group by AGE,GENDER_CODE)
select AGE,GENDER,
ROUND(H_AVG,1) as H_AVG,
ROUND(H_PER*100,1) || '%' as H_PER,
ROUND(W_AVG,1) as W_AVG,
ROUND(W_PER*100,1) || '%' as W_PER
from tmp3
order by AGE desc,GENDER_CODE desc