with SCHOOL_HEALTH_H as(
    select
        AGE,
        GENDER_CODE,
        AVERAGE_VALUE as H
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR = 2019
    and CATEGORY_CODE = '10'
    
), SCHOOL_HEALTH_W as(
    select
        AGE,
        GENDER_CODE,
        AVERAGE_VALUE as W
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR = 2019
    and CATEGORY_CODE = '20'
    
), SCHOOL_HEALTH_HW as(
    select
        h.AGE,
        h.GENDER_CODE,
        H,
        W
    from
        SCHOOL_HEALTH_H h
    join
        SCHOOL_HEALTH_W w
    on
        h.AGE = w.AGE
    and h.GENDER_CODE = w.GENDER_CODE
)

select
    AGE     as AGE,
    case GENDER_CODE
        when '20' then 'MALE'
        when '30' then 'FEMALE'
        end as GENDER,
    round(avg(H), 1) as H_AVG,
    -- 身長平均値以上の割合 as H_PER
    round(avg(W), 1) as W_AVG
    -- 体重平均値以上の割合 as W_PER
from
    SCHOOL_HEALTH_HW
group by
    AGE,
    GENDER_CODE
order by
    AGE         desc,
    GENDER_CODE desc