with TBL_AVG as (
select
    CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
    ,round(avg(AVERAGE_VALUE),1) as AVG_VAL
from
    SCHOOL_HEALTH
where
    SURVEY_YEAR=2019
group by 1,2,3
)


,JOIN1 as (
select
    S.AGE
    ,PF_CODE
    ,S.GENDER_CODE
    ,S.CATEGORY_CODE
    ,T.AVG_VAL
    ,sum(case when AVERAGE_VALUE>=AVG_VAL then 1 else 0 end) as CNT
    ,count(*) as CNT_ALL
from
    SCHOOL_HEALTH S 
    inner join 
        TBL_AVG T
        on 
            S.AGE=T.AGE
            and S.GENDER_CODE=T.GENDER_CODE
            and S.CATEGORY_CODE=T.CATEGORY_CODE
group by 1,2,3,4
)

,JOIN2 as (
select
    AGE
    ,GENDER_CODE
    ,CATEGORY_CODE
    ,AVG_VAL
    ,round(100.0*sum(CNT)/sum(CNT_ALL), 1) as PER
from 
    JOIN1
group by 1,2,3,4
)


, JOIN3 as (
select
    AGE
    ,GENDER_CODE
    ,sum(case when CATEGORY_CODE='10' then AVG_VAL end) as H_AVG
    ,cast(sum(case when CATEGORY_CODE='10' then PER end) as varchar)||'%' as H_PER
    ,sum(case when CATEGORY_CODE='20' then AVG_VAL end) as W_AVG
    ,cast(sum(case when CATEGORY_CODE='20' then PER end) as varchar)||'%' as W_PER
from
    JOIN2
group by 1,2
)

select
    AGE
    ,case
        when GENDER_CODE='20' then 'MALE'
        when GENDER_CODE='30' then 'FEMALE'
        end as GENDER
    ,H_AVG
    ,H_PER
    ,W_AVG
    ,W_PER
from
    JOIN3
order by 1 desc, GENDER_CODE desc