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
    ),
    temp2h 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
        
    from
        SCHOOL_HEALTH
    where
        SURVEY_YEAR = 2019
    GROUP BY
        age
        ,gender_code
        ,PF_CODE
    ),
    temp2w 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 = '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
        t1.age
        ,t1.gender
        ,t1.H_AVG
        ,t1.W_AVG
        ,CASE WHEN t2h.HEIGHT >= t1.H_AVG THEN 1 ELSE 0 END AS h_flg
        ,CASE WHEN t2w.WEIGHT >= t1.W_AVG THEN 1 ELSE 0 END AS w_flg
    from
        temp2h t2h
    inner join temp t1
        on t2h.AGE = t1.AGE
        and t2h.GENDER = t1.GENDER
    inner join temp2w t2w
        on t2w.AGE = t1.AGE
        and t2w.GENDER = t1.GENDER
    
    )

select
    MAX(age) AS AGE
    ,MAX(gender) AS GENDER
    ,MAX(H_AVG) AS H_AVG
    ,cast(ROUND(cast(SUM(h_flg) as real) * 100/COUNT(*),1) as varchar) || '%' AS H_PER
    ,MAX(W_AVG) AS W_AVG
    ,cast(ROUND(cast(SUM(w_flg) as real) * 100/COUNT(*),1) as varchar) || '%' AS W_PER
from 
    temp3
GROUP BY
    age
    ,gender
order by
    age desc
    ,gender asc
;