with heights as (
select PF_CODE, GENDER_CODE, AGE, AVERAGE_VALUE as AVG_HEIGHT, avg(AVERAGE_VALUE) over (partition by GENDER_CODE, AGE) as ALL_AVG_HEIGHT
from SCHOOL_HEALTH
where CATEGORY_CODE = '10' and survey_year = 2019
)
, heights2 as (
select *, case ROUND(AVG_HEIGHT, 1) >= ROUND(ALL_AVG_HEIGHT,1) when 1 then 1 else null end as OV1
from heights
)
, weights as (
select PF_CODE, GENDER_CODE, AGE, AVERAGE_VALUE as AVG_WEIGHT, avg(AVERAGE_VALUE) over (partition by GENDER_CODE, AGE) as ALL_AVG_WEIGHT
from SCHOOL_HEALTH
where CATEGORY_CODE = '20' and survey_year = 2019
)
, weights2 as (
select *, case ROUND(AVG_WEIGHT, 1) >= ROUND(ALL_AVG_WEIGHT, 1) when 1 then 1 else null end as OV2
from weights
)
-- select * from SCHOOL_HEALTH where category_code = '20' and survey_year = 2019 and GENDER_CODE = 30 and age = 17

-- select * from weights2 where GENDER_CODE = 30 and AGE = 17

-- 17, female
select 
heights2.AGE
, case heights2.GENDER_CODE when '20' then 'MALE' else 'FEMALE' end as GENDER
, ROUND(ALL_AVG_HEIGHT, 1) as H_AVG
, ROUND(100.0 * COUNT(OV1) / COUNT(), 1) || '%' as H_PER
, ROUND(ALL_AVG_WEIGHT, 1) as W_AVG
, ROUND(100.0 * COUNT(OV2) / COUNT(), 1) || '%' as W_PER
from heights2 join weights2 on heights2.GENDER_CODE = weights2.GENDER_CODE and heights2.AGE = weights2.AGE
group by heights2.GENDER_CODE, heights2.AGE
order by heights2.AGE desc, heights2.GENDER_CODE desc