WITH tmp AS (
    SELECT AGE, GENDER_CODE,
        ROUND(AVG(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE ELSE NULL END), 1) H_AVG,
        ROUND(AVG(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE ELSE NULL END), 1) W_AVG
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    GROUP BY AGE, GENDER_CODE
)
SELECT s.AGE,
    CASE WHEN s.GENDER_CODE = '20' THEN 'MALE' ELSE 'FEMALE' END GENDER,
    H_AVG,
    ROUND(AVG(
        CASE WHEN CATEGORY_CODE = '10'
        THEN (
            CASE WHEN AVERAGE_VALUE >= (SELECT H_AVG FROM tmp t WHERE t.AGE = s.AGE AND t.GENDER_CODE = s.GENDER_CODE)
            THEN 1
            ELSE 0
            END
        )
        ELSE NULL
        END
    ) * 100, 1) || '%' H_PER,
    W_AVG,
    ROUND(AVG(
        CASE WHEN CATEGORY_CODE = '20'
        THEN (
            CASE WHEN AVERAGE_VALUE >= (SELECT W_AVG FROM tmp t WHERE t.AGE = s.AGE AND t.GENDER_CODE = s.GENDER_CODE)
            THEN 1
            ELSE 0
            END
        )
        ELSE NULL
        END
    ) * 100, 1) || '%' W_PER
FROM SCHOOL_HEALTH s
LEFT JOIN tmp ON s.AGE = tmp.AGE AND s.GENDER_CODE = tmp.GENDER_CODE
WHERE SURVEY_YEAR = 2019
GROUP BY s.AGE, GENDER
ORDER BY s.AGE DESC, s.GENDER_CODE DESC