with m_h_avg as (
select
age
,gender_code
,round(avg(average_value),1) as avg
,count(*) as total
from school_health
where survey_year ='2019'
and gender_code = '20'
and category_code ='10'
group by age
) 
,f_h_avg as (
select
age,
gender_code
,round(avg(average_value),1) as avg
,count(*) as total
from school_health
where survey_year ='2019'
and gender_code = '30'
and category_code ='10'
group by age
)
,m_w_avg as (
select
age
,gender_code
,round(avg(average_value),1) as avg
,count(*) as total
from school_health
where survey_year ='2019'
and gender_code = '20'
and category_code ='20'
group by age
) 
,f_w_avg as (
select
age,
gender_code
,round(avg(average_value),1) as avg
,count(*) as total
from school_health
where survey_year ='2019'
and gender_code = '30'
and category_code ='20'
group by age
)
,result as (
select
s.age as AGE
,s.category_code
,case 
    when s.gender_code = '20' then 'MALE'
    when s.gender_code = '30' then 'FEMALE'
end as GENDER
,case 
    when s.gender_code = '20' then mha.avg
    when s.gender_code = '30' then fha.avg
end as H_AVG
,case 
    when s.gender_code = '20' then 
        round(sum(case when average_value >= mha.avg then 1 else 0 end) *100.0/mha.total,1)
    when s.gender_code = '30' then 
        round(sum(case when average_value >= fha.avg then 1 else 0 end) *100.0/fha.total,1)
end || '%'as H_PER
,case 
    when s.gender_code = '20' then mwa.avg
    when s.gender_code = '30' then fwa.avg
end as W_AVG
,case 
    when s.gender_code = '20' then 
        round(sum(case when average_value >= mwa.avg then 1 else 0 end) *100.0/mwa.total,1)
    when s.gender_code = '30' then 
        round(sum(case when average_value >= fwa.avg then 1 else 0 end) *100.0/fwa.total,1)
end  || '%' as W_PER
from school_health as s
left join m_h_avg as mha
on s.age=mha.age
and s.gender_code=mha.gender_code
and s.category_code ='10'
left join f_h_avg as fha
on s.age=fha.age
and s.gender_code=fha.gender_code
and s.category_code ='10'
left join m_w_avg as mwa
on s.age=mwa.age
and s.gender_code=mwa.gender_code
and s.category_code ='20'
left join f_w_avg as fwa
on s.age=fwa.age
and s.gender_code=fwa.gender_code
and s.category_code ='20'
where s.survey_year='2019'
group by s.age,s.gender_code,category_code
order by age desc,s.gender_code desc)
select 
r1.AGE
,r1.GENDER
,r1.H_AVG
,r1.H_PER
,r2.W_AVG
,r2.W_PER
from result as r1
inner join result as r2
on r1.age=r2.age
and r1.gender=r2.gender
and r1.category_code='10'
and r2.category_code='20'