with tmp as (
    SELECT
    AGE as AGE,
    GENDER_CODE as GENDER_CODE,
    CATEGORy_CODE,
    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 1,2
    ),
    tmp2 as
    (select 
    tmp.AGE as AGE,
    case 
     when tmp.GENDER_CODE='20' THEN 'MALE'
     when tmp.GENDER_CODE='30' THEN 'FEMALE'
    END as GENDER,
    H_AVG,
    round(
    sum(
    case when average_value >= H_AVG
    and school_health.category_code='10'
    then 1
    else 0
    END
    )*100.0  /
    sum(
    case when school_health.category_code='10' then 1
    else 0
    end ),1 ) || '%' as H_PER,
    round(
    sum(
    case when average_value >= W_AVG
    and school_health.category_code='20'
    then 1
    else 0
    END
    )*100.0  /
    sum(
    case when school_health.category_code='20' then 1
    else 0
    end ),1 ) || '%' as W_PER,
    W_AVG as W_W
    from tmp inner join SCHOOL_HEALTH
    on tmp.Age = SCHOOL_HEALTH.age and  tmp.gender_code=school_health.gender_code
    where school_health.survey_year = 2019
    group by 1,2 )
    select 
    AGE,
    GENDER,
    H_AVG,H_PER,W_W as W_AVG,W_PER
    from tmp2
    order by 1 desc,2 asc