with height as(
    select
        PF_CODE,
        AGE,
        GENDER_CODE,
        AVERAGE_VALUE,
        round(
            avg(AVERAGE_VALUE) over(partition by AGE, GENDER_CODE),
            1
        ) as H_AVG
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR = 2019
        and CATEGORY_CODE = '10'
),
weight as(
    select
        PF_CODE,
        AGE,
        GENDER_CODE,
        AVERAGE_VALUE,
        round(
            avg(AVERAGE_VALUE) over(partition by AGE, GENDER_CODE),
            1
        ) as W_AVG
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR = 2019
        and CATEGORY_CODE = '20'
)
select
    height.AGE as AGE,
    case
        when height.GENDER_CODE = '20' then 'MALE'
        when height.GENDER_CODE = '30' then 'FEMALE'
    end as GENDER,
    height.H_AVG,
    round(
        cast(sum(
            case
                when height.AVERAGE_VALUE >= height.H_AVG then 1
            end
        ) as real) / cast(count(height.PF_CODE) as real) * 100,
        1
    ) || '%' as H_PER,
    weight.W_AVG,
    round(
        cast(sum(
            case
                when weight.AVERAGE_VALUE >= weight.W_AVG then 1
            end
        ) as real) / cast(count(weight.PF_CODE) as real) * 100,
        1
    ) || '%' as W_PER
from
    height
    inner join weight on height.AGE = weight.AGE
    and height.GENDER_CODE = weight.GENDER_CODE
group by
    height.AGE,
    height.GENDER_CODE
order by
    height.AGE desc,
    height.GENDER_CODE desc;