with avg_all as 
(
    select
        s.CATEGORY_CODE
        ,s.GENDER_CODE
        ,s.AGE
        ,round(avg(s.AVERAGE_VALUE),1) as avg--全体の平均値
        ,count(1) as num_area --地域数
    from SCHOOL_HEALTH s
    where s.SURVEY_YEAR = 2019
    group by 1,2,3
)
,calc_rate as (
    select
        s.AGE
        ,s.GENDER_CODE
        ,s.CATEGORY_CODE
        ,a.avg
        ,sum(CASE WHEN s.AVERAGE_VALUE >= a.avg then 1 else 0 end)/cast(a.num_area as real) as rate
    from SCHOOL_HEALTH s
    left join avg_all a
        on s.CATEGORY_CODE = a.CATEGORY_CODE
        and s.GENDER_CODE = a.GENDER_CODE
        and s.AGE = a.AGE
    group by 1,2,3
)

select
    ch.AGE
    ,CASE WHEN ch.GENDER_CODE like '20' then 'MALE'
        WHEN ch.GENDER_CODE like '30' then 'FEMALE'
        end as GENDER
    ,ch.H_AVG
    ,ch.H_PER
    ,cw.W_AVG
    ,cw.W_PER
FROM (
    select
        c.AGE
        ,c.GENDER_CODE
        ,c.AVG as H_AVG
        ,round(c.rate * 100, 1)||'%' as H_PER
    from calc_rate c
    where c.CATEGORY_CODE = '10'
) ch
LEFT JOIN (
    select
        c.AGE
        ,c.GENDER_CODE
        ,c.AVG as W_AVG
        ,round(c.rate * 100, 1)||'%' as W_PER
    from calc_rate c
    where c.CATEGORY_CODE = '20'
) cw
    ON cw.AGE = ch.AGE
    and cw.GENDER_CODE = ch.GENDER_CODE
order by cw.AGE desc,cw.gender_code desc