with s as (select * from school_health where survey_year = 2019)
, sw as (
select * from s
where CATEGORY_CODE = 20
)
, sh as (
select * from s
where CATEGORY_CODE = 10
)
, swa as (
select 
AGE
, GENDER_CODE
, ROUND(avg(AVERAGE_VALUE), 1) as WA
, COUNT(1) as CNT
from sw
group by AGE, GENDER_CODE
)
, sha as (
select
AGE
, GENDER_CODE
, ROUND(avg(AVERAGE_VALUE), 1) as HA
, COUNT(1) as CNT
from sh
group by AGE, GENDER_CODE
)
, swap as (
select 
sw.AGE as AGE
, sw.GENDER_CODE as GENDER_CODE
, swa.WA as WA
, swa.CNT as CNT
, count(case when sw.AVERAGE_VALUE >= swa.WA then 1 end) as OCNT
from sw join swa on sw.GENDER_CODE = swa.GENDER_CODE and sw.AGE = swa.AGE
group by sw.GENDER_CODE, sw.AGE
)
, swap2 as (
select *, ROUND(100.0 * OCNT / CNT, 1) as W_PER from swap
)
, shap as (
select 
sh.AGE as AGE
, sh.GENDER_CODE as GENDER_CODE
, sha.HA as HA
, sha.CNT as CNT
, count(case when sh.AVERAGE_VALUE >= sha.HA then 1 end) as OCNT
from sh join sha on sh.GENDER_CODE = sha.GENDER_CODE and sh.AGE = sha.AGE
group by sh.GENDER_CODE, sh.AGE
)
, shap2 as (
select *, ROUND(100.0 * OCNT / CNT, 1) as H_PER from shap
)
select 
shap2.AGE as AGE
, case shap2.GENDER_CODE when 20 then 'MALE' else 'FEMALE' end as GENDER
, shap2.HA as H_AVG
, shap2.H_PER || '%' as H_PER
, swap2.WA as W_AVG
, swap2.W_PER || '%' as W_PER
from shap2 join swap2 on shap2.AGE = swap2.AGE and shap2.GENDER_CODE = swap2.GENDER_CODE
order by AGE desc, shap2.GENDER_CODE desc