with base as (
select
    PF_CODE
    ,CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
    ,round(avg(AVERAGE_VALUE) over (partition by CATEGORY_CODE,GENDER_CODE,AGE),1) as X_AVG
    ,case when round(avg(AVERAGE_VALUE) over (partition by CATEGORY_CODE,GENDER_CODE,AGE),1) <= AVERAGE_VALUE then 1 else 0 end as F
from
    SCHOOL_HEALTH   
where
    SURVEY_YEAR=2019
)
,base2 as (
select
    CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
    ,X_AVG
    ,round(100.0*sum(F)/count(),1)||"%" as X_PER
from
    base
group by
    CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
    ,X_AVG
)
select
    b1.AGE
    ,case when b1.GENDER_CODE ="20" then "MALE" else "FEMALE" end as GENDER
    ,b1.X_AVG as H_AVG
    ,b1.X_PER as H_PER
    ,b2.X_AVG as W_AVG
    ,b2.X_PER as W_PER
from
    base2 as b1
    inner join
    base2 as b2
    on b1.AGE=b2.AGE and b1.GENDER_CODE = b2.GENDER_CODE and b1.CATEGORY_CODE=10 and b2.CATEGORY_CODE=20
order by
    b1.AGE desc
    ,b1.GENDER_CODE desc