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,
        
        W_AVG ,
        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
        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 
        * from tmp2
        order by 1 desc,2 asc