with tmp as(
select
    AGE
    ,GENDER_CODE
    ,CATEGORY_CODE
    ,AVG(AVERAGE_VALUE) AS avg
from
    SCHOOL_HEALTH
where
    SURVEY_YEAR = 2019
group by
    AGE
    ,GENDER_CODE
    ,CATEGORY_CODE
), 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
(
    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
)
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 tmp2 as w
on
    h.AGE = w.AGE
    and 
    h.GENDER_CODE = w.GENDER_CODE
    
where
    h.CATEGORY_CODE = 10
    and
    w.CATEGORY_CODE = 20
ORDER BY
    h.AGE desc
    ,h.GENDER_CODE desc