with sub as (
    select
        pf_code,
        age,
        gender_code,
        (select avg(average_value) from school_health s2 where survey_year = 2019 and category_code = 10 and s1.pf_code = s2.pf_code and s1.age = s2.age and s1.gender_code = s2.gender_code) as height,
        (select avg(average_value) from school_health s2 where survey_year = 2019 and category_code = 20 and s1.pf_code = s2.pf_code and s1.age = s2.age and s1.gender_code = s2.gender_code) as weight
    from school_health s1
    where survey_year = 2019
    group by pf_code, age, gender_code
)

select
    s1.age as AGE,
    case when s1.gender_code = '20' then 'MALE' else 'FEMALE' end as GENDER,
    round(avg(s1.height), 1) as H_AVG,
    round(100 * (select count(*) from sub s2 where s2.age = s1.age and s2.gender_code = s1.gender_code and round(s2.height, 1) >= avg(s1.height)) / (select count(*) from sub s3 where s3.age = s1.age and s3.gender_code = s1.gender_code and height is not null), 1) || '%' as H_PER,
    round(avg(s1.weight), 1) as W_AVG,
    round(100 * (select count(*) from sub s2 where s2.age = s1.age and s2.gender_code = s1.gender_code and round(s2.weight, 1) >= avg(s1.weight)) / (select count(*) from sub s3 where s3.age = s1.age and s3.gender_code = s1.gender_code and weight is not null), 1) || '%' as W_PER
from sub s1
group by s1.age, s1.gender_code
order by s1.age desc, s1.gender_code desc;