with AVG_TBL(AGE, GENDER_CODE, GENDER, H_AVG, W_AVG) as (
    select
        AGE
        , GENDER_CODE
        , case
            when GENDER_CODE='20' then 'MALE'
            when GENDER_CODE='30' then 'FEMALE'
        end
        , round(avg(case
                when CATEGORY_CODE='10' then AVERAGE_VALUE
                else NULL
            end), 1)
        , round(avg(case
                when CATEGORY_CODE='20' then AVERAGE_VALUE
                else NULL
            end), 1)
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR=2019
    group by
        AGE
        , GENDER_CODE
)
, CNT_TBL(AGE, GENDER_CODE, H_CNT, W_CNT) as (
    select
        S.AGE
        , S.GENDER_CODE
        , cast(sum(case
                when S.CATEGORY_CODE='10' and S.AVERAGE_VALUE>=A.H_AVG then 1
                else 0
            end) as real)
        , cast(sum(case
                when S.CATEGORY_CODE='20' and S.AVERAGE_VALUE>=A.W_AVG then 1
                else 0
            end) as real)
    from 
        SCHOOL_HEALTH as S
    left join 
        AVG_TBL as A on S.AGE=A.AGE and S.GENDER_CODE=A.GENDER_CODE
    where
        S.SURVEY_YEAR=2019
    group by
        S.AGE
        , S.GENDER_CODE
)

select
    A.AGE as AGE
    , A.GENDER as GENDER
    , A.H_AVG as H_AVG
    , round((S.H_CNT / P.PF_H_CNT) * 100, 1) || '%' as H_PER
    , A.W_AVG as W_AVG
    , round((S.W_CNT / P.PF_W_CNT) * 100, 1) || '%' as W_PER
from
    AVG_TBL as A
left join 
    CNT_TBL as S on A.AGE=S.AGE and A.GENDER_CODE=S.GENDER_CODE
left join
    (
        select
            AGE
            , GENDER_CODE
            , sum
                (
                case
                    when CATEGORY_CODE='10' then 1
                    else 0
                end
                ) as PF_H_CNT
            , sum
                (
                case
                    when CATEGORY_CODE='20' then 1
                    else 0
                end
                ) as PF_W_CNT
        from 
            SCHOOL_HEALTH
        where
            SURVEY_YEAR=2019
        group by
            AGE
            , GENDER_CODE
    ) as P on A.AGE=P.AGE and A.GENDER_CODE=P.GENDER_CODE
group by
    A.AGE
    , A.GENDER
order by
    A.AGE desc
    , S.GENDER_CODE desc
;