WITH AVG_TABLE AS
(SELECT AGE, GENDER_CODE,
ROUND(AVG(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE END), 1) AS H_AVG,
ROUND(AVG(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE END), 1) AS W_AVG
FROM SCHOOL_HEALTH
WHERE SURVEY_YEAR = 2019
GROUP BY AGE, GENDER_CODE)
SELECT AT.AGE,
CASE WHEN AT.GENDER_CODE = 20 THEN 'MALE' ELSE 'FEMALE' END AS GENDER,
H_AVG,
ROUND(100.0 * COUNT(CASE WHEN SH.CATEGORY_CODE = '10' AND SH.AVERAGE_VALUE >= H_AVG THEN 1 END) /
COUNT(CASE WHEN SH.CATEGORY_CODE = '10' THEN 1 END), 1) || '%'
AS H_PER,
W_AVG,
ROUND(100.0 * COUNT(CASE WHEN SH.CATEGORY_CODE = '20' AND SH.AVERAGE_VALUE >= W_AVG THEN 1 END) /
COUNT(CASE WHEN SH.CATEGORY_CODE = '20' THEN 1 END), 1) || '%'
AS W_PER
FROM AVG_TABLE AT LEFT JOIN SCHOOL_HEALTH SH
ON AT.AGE = SH.AGE AND AT.GENDER_CODE = SH.GENDER_CODE
AND SH.SURVEY_YEAR = 2019
GROUP BY AT.AGE, AT.GENDER_CODE
ORDER BY AT.AGE DESC, AT.GENDER_CODE DESC