with 
W as (
    select *
    from SCHOOL_HEALTH
    where CATEGORY_CODE = '20' and SURVEY_YEAR = 2019
)
,
H as (
    select *
    from SCHOOL_HEALTH
    where CATEGORY_CODE = '10' and SURVEY_YEAR = 2019
)
,
P as (
    select PF_CODE, GENDER_CODE, AGE,
        avg(W.AVERAGE_VALUE) as W_AVG,
        avg(H.AVERAGE_VALUE) as H_AVG
    from W full join H using (PF_CODE, GENDER_CODE, AGE)
    group by PF_CODE, GENDER_CODE, AGE
)
,
A as (
    select GENDER_CODE, AGE,
        avg(W.AVERAGE_VALUE) as W_AVG,
        avg(H.AVERAGE_VALUE) as H_AVG
    from W full join H using (PF_CODE, CATEGORY_CODE, GENDER_CODE, AGE)
    group by GENDER_CODE, AGE
)
,
R as (
    select
        AGE,
        GENDER_CODE,
        A.W_AVG,
        A.H_AVG,
        cast(sum(P.W_AVG > A.W_AVG) as real) * 100 / sum(1) as W_PER,
        cast(sum(P.H_AVG > A.H_AVG) as real) * 100 / sum(1) as H_PER
    from A inner join P using (GENDER_CODE, AGE)
    group by GENDER_CODE, AGE
)
select
    AGE,
    case GENDER_CODE
      when '20' then 'MALE'
      when '30' then 'FEMALE'
    end as GENDER,
    round(H_AVG, 1) as H_AVG,
    round(H_PER, 1) as H_PER,
    round(W_AVG, 1) as W_AVG,
    round(W_PER, 1) as W_PER
from R
order by AGE desc, GENDER_CODE desc