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 AVG_HEIGHT > ALL_AVG_HEIGHT 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 AVG_WEIGHT > ALL_AVG_WEIGHT when 1 then 1 else null end as OV2
from weights
)
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


-- , weights as (
-- select PF_CODE, GENDER_CODE, AGE, AVERAGE_VALUE as AVG_WEIGHT
-- from SCHOOL_HEALTH
-- where CATEGORY_CODE = '20' and survey_year = 2019
-- )
-- , avg_heights as (
-- select AGE, GENDER_CODE, avg(AVG_HEIGHT), ALL_AVG_HEIGHT
-- from heights 
-- group by GENDER_CODE, AGE
-- )
-- select * from avg_heights order by AGE desc, GENDER_CODE desc