with t1 as (
select AGE, CATEGORY_CODE, GENDER_CODE, avg(AVERAGE_VALUE) AS avg_v
from SCHOOL_HEALTH
where SURVEY_YEAR = 2019
group by 1, 2, 3
), t2 as (
select s.AGE, s.CATEGORY_CODE, s.GENDER_CODE, round(t1.avg_v, 1) as r_a, round(COUNT(CASE WHEN s.AVERAGE_VALUE >= t1.avg_v THEN 1 ELSE NULL END) * 1000.0 / count(1)) / 10.0 as per
from SCHOOL_HEALTH s
left join t1
on s.AGE = t1.AGE and s.CATEGORY_CODE = t1.CATEGORY_CODE and s.GENDER_CODE = t1.GENDER_CODE
group by 1, 2, 3, 4
), h as (
select AGE, GENDER_CODE,  r_a as H_AVG, cast(per as VARCHER) || '%'as H_PER
from t2
where CATEGORY_CODE = '10'
), w as (
select AGE, GENDER_CODE,  r_a as W_AVG, cast(per as VARCHER) || '%' as W_PER
from t2
where CATEGORY_CODE = '20'
)
select 
    h.AGE,
    CASE 
        WHEN h.GENDER_CODE = '20' THEN 'MALE'
        ELSE 'FEMALE'
    END as GENDER,
    h.H_AVG, h.H_PER, w.W_AVG, w.W_PER
from h
left join w
on h.AGE = w.AGE and h.GENDER_CODE = w.GENDER_CODE
order by 1 desc, 2