with sub as (
    select
        age,
        gender_code,
        round(avg(case when category_code = '10' then average_value end), 1) as h_avg,
        round(avg(case when category_code = '20' then average_value end), 1) as w_avg
    from school_health
    where survey_year = 2019
    group by age, gender_code
)

select
    sh.age AS AGE,
    (case when sh.gender_code = '20' then 'MALE' else 'FEMALE' end) as GENDER,
    max(sub.h_avg) as H_AVG,
    round(100.0 * count(case when sh.category_code = '10' and sh.average_value >= sub.h_avg then 1 end) / count(case when sh.category_code = '10' then 1 end), 1) || '%' as H_PER,
    max(sub.w_avg) as W_AVG,
    round(100.0 * count(case when sh.category_code = '20' and sh.average_value >= sub.w_avg then 1 end) / count(case when sh.category_code = '20' then 1 end), 1) || '%' as W_PER
from school_health sh
join sub on sh.survey_year = 2019 and sh.age = sub.age and sh.gender_code = sub.gender_code
group by sh.age, sh.gender_code
order by sh.age DESC, sh.gender_code desc;