with tmp as(
select AGE ,GENDER_CODE ,CATEGORY_CODE ,ROUND(AVG(AVERAGE_VALUE),1) AS avg
from SCHOOL_HEALTH
where SURVEY_YEAR = 2019
group by AGE ,GENDER_CODE ,CATEGORY_CODE
), tmp3 as(
select
    s.AGE ,s.GENDER_CODE ,s.CATEGORY_CODE ,s.AVERAGE_VALUE
    ,CASE
        WHEN s.AVERAGE_VALUE >= t.avg THEN 1  ELSE 0 END AS cnt
from SCHOOL_HEALTH as s

inner join tmp as t
    on  s.AGE = t.AGE and s.GENDER_CODE = t.GENDER_CODE and s.CATEGORY_CODE = t.CATEGORY_CODE
where s.SURVEY_YEAR = 2019
),tmp2 as(
select AGE ,GENDER_CODE ,CATEGORY_CODE
    ,ROUND((sum(cnt) * 100.0 / count(1)), 1) || '%' as rate
    ,ROUND(AVG(AVERAGE_VALUE),1) AS avg
from
    tmp3
group by
    AGE ,GENDER_CODE ,CATEGORY_CODE
)
select
    h.AGE
    ,CASE
        WHEN h.GENDER_CODE = '20' THEN 'MALE'
        WHEN h.GENDER_CODE = '30' THEN 'FEMALE'
    END AS GENDER
    ,h.avg as H_AVG
    ,h.rate as H_PER
    ,w.avg as W_AVG
    ,w.rate as W_PER
from tmp2 as h
inner join (select * from tmp2 where CATEGORY_CODE = 20) as w
on
    h.AGE = w.AGE
    and 
    h.GENDER_CODE = w.GENDER_CODE
    
where
    h.CATEGORY_CODE = 10

ORDER BY
    h.AGE desc
    ,h.GENDER_CODE desc