with v as (
with u as (
with t as (
select category_code as cc,gender_code as gc, age, round(avg(average_value),1) as a
from school_health as s
where survey_year=2019
group by 1,2,3
)
select pf_code as pc,category_code as cc,gender_code as gc, s.age, avg(average_value) as b, a
from school_health as s
inner join t on
t.cc=s.category_code and t.gc=s.gender_code and t.age=s.age
where survey_year=2019
group by 1,2,3,4
)
select cc,gc,age,round(sum(case when b>=a then 1 else 0 end)*100.0/count(*),1)||'%' as r,a
from u
group by 1,2,3
)
select v.age as 'AGE', case when v.gc='20' then 'MALE' else 'FEMALE' end as 'GENDER',
v.a as 'H_AVG', v.r as 'H_PER', w.a as 'W_AVG', w.r as 'W_PER'
from v inner join v as w 
on v.age=w.age and v.gc=w.gc and w.cc='20'
where v.cc='10'
order by 1 desc,2