with school_health_2019 as (
    select *
    from SCHOOL_HEALTH
    where SURVEY_YEAR = 2019
)
, calc_avg as (
    select
        AGE
        , GENDER_CODE
        , CATEGORY_CODE
        , round(avg(AVERAGE_VALUE), 1) as avg
    from school_health_2019
    group by 
        AGE
        , GENDER_CODE
        , CATEGORY_CODE
)
select
    sh.AGE
    , case
        when sh.GENDER_CODE = '20' then 'MALE'
        when sh.GENDER_CODE = '30' then 'FEMALE' end as GENDER
    , max(h.avg) as H_AVG
    , round(100.0 * sum(case when sh.CATEGORY_CODE='10' and AVERAGE_VALUE>=h.avg then 1 else 0 end) / sum(case when sh.CATEGORY_CODE='10' then 1 else 0 end), 1) || '%' as H_PER
    , max(w.avg) as W_AVG
    , round(100.0 * sum(case when sh.CATEGORY_CODE='20' and AVERAGE_VALUE>=w.avg then 1 else 0 end) / sum(case when sh.CATEGORY_CODE='20' then 1 else 0 end), 1) || '%' as W_PER
from
    school_health_2019 sh
    left outer join(
        select *
        from calc_avg
        where CATEGORY_CODE = '10'
    ) h
        on sh.AGE = h.AGE
        and sh.GENDER_CODE = h.GENDER_CODE
        and sh.CATEGORY_CODE = h.CATEGORY_CODE
    left outer join (
        select *
        from calc_avg
        where CATEGORY_CODE = '20'
    ) w
        on sh.AGE = w.AGE
        and sh.GENDER_CODE = w.GENDER_CODE
        and sh.CATEGORY_CODE = w.CATEGORY_CODE
group by
    sh.AGE
    , case
        when sh.GENDER_CODE = '20' then 'MALE'
        when sh.GENDER_CODE = '30' then 'FEMALE' end
order by 
    sh.AGE desc
    , sh.GENDER_CODE desc
;