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


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