with temp as(
    select 
        age AS AGE
        ,CASE WHEN gender_code = '20' THEN 'MALE'
            WHEN gender_code = '30' THEN 'FEMALE'
            ELSE NULL END AS GENDER
        ,round(AVG(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE ELSE NULL END),1) AS H_AVG
        ,round(AVG(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE ELSE NULL END),1) AS W_AVG
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR = 2019
    group by
        age
        ,gender_code
    ),
    temp2 as(
    select 
        age AS AGE
        ,CASE WHEN gender_code = '20' THEN 'MALE'
            WHEN gender_code = '30' THEN 'FEMALE'
            ELSE NULL END AS GENDER
        ,SUM(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE ELSE NULL END) AS HEIGHT
        ,SUM(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE ELSE NULL END) AS WEIGHT
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR = 2019
    GROUP BY
        age
        ,gender_code
        ,PF_CODE
    ),
    temp3 as (
    select
        t2.age
        ,t2.gender
        ,t1.H_AVG
        ,t1.W_AVG
        ,CASE WHEN HEIGHT >= t1.H_AVG THEN 1 ELSE 0 END AS h_flg
        ,CASE WHEN WEIGHT >= t1.W_AVG THEN 1 ELSE 0 END AS w_flg
    from
        temp2 t2
    inner join temp t1
        on t2.AGE = t1.AGE
        and t2.GENDER = t1.GENDER
    )

select
    MAX(age)
    ,MAX(gender)
    ,MAX(H_AVG)
    ,ROUND(cast(SUM(h_flg) as real) * 100/COUNT(*),1) AS H_PER
    ,MAX(W_AVG)
    --,
from 
    temp3
GROUP BY
    age
    ,gender
order by
    age desc
    ,gender asc
    
--わからんす・・・
;