with h 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'
),
w 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
    h.AGE as AGE,
    case
        when h.GENDER_CODE = '20' then 'MALE'
        when h.GENDER_CODE = '30' then 'FEMALE'
    end as GENDER,
    h.H_AVG,
    round(
        sum(
            case
                when h.AVERAGE_VALUE >= h.H_AVG then 1
                else 0
            end
        ) / cast(count(h.PF_CODE) as real) * 100,
        1
    )   || '%' as H_PER,
    w.W_AVG,
    round(
        sum(
            case
                when w.AVERAGE_VALUE >= w.w_AVG then 1
            end
        ) / cast(count(w.PF_CODE) as real) * 100,
        1
    )  || '%' as W_PER
from
    h
    inner join w on h.AGE = w.AGE
    and h.GENDER_CODE = w.GENDER_CODE
group by
    h.AGE,
    h.GENDER_CODE
order by
    h.AGE desc,
    h.GENDER_CODE desc