with
    h as (
        select
            pf_code
            ,gender_code
            ,age
            ,average_value as height
            ,round(cast(avg(average_value) over(partition by gender_code, age) as real), 1) as h_avg
        from
            school_health
        where
            survey_year = 2019
            and category_code = '10'
        order by
            2, 3, 1
    )
    ,w as (
        select
            pf_code
            ,gender_code
            ,age
            ,average_value as weight
            ,round(cast(avg(average_value) over(partition by gender_code, age) as real), 1) as w_avg
        from
            school_health
        where
            survey_year = 2019
            and category_code = '20'
        order by
            2, 3, 1
    )
    ,ht as (
        select
            gender_code
            ,age
            ,h_avg
            ,count(case when height >= h_avg then 1 else null end) as higher_cnt
            ,count(pf_code) as cnt
        from
            h
        group by
            1, 2, 3
    )
    ,wt as (
        select
            gender_code
            ,age
            ,w_avg
            ,count(case when weight >= w_avg then 1 else null end) as higher_cnt
            ,count(pf_code) as cnt
        from
            w
        group by
            1, 2, 3
    )
select
    ht.age as AGE
    ,case
        when ht.gender_code = '20' then 'MALE'
        when ht.gender_code = '30' then 'FEMALE'
        end as GENDER
    ,ht.h_avg as H_AVG
    ,round(cast(ht.higher_cnt * 100 as real) / cast(ht.cnt as real), 1) || '%' as H_PER
    ,wt.w_avg as W_AVG
    ,round(cast(wt.higher_cnt * 100 as real) / cast(wt.cnt as real), 1) || '%' as W_PER
from
    ht
    join
        wt
    on
        ht.gender_code = wt.gender_code
        and ht.age = wt.age
order by
    1 desc, ht.gender_code desc