SELECT 
    s.AGE AS AGE,
    CASE 
        WHEN s.GENDER_CODE = 20 THEN 'MALE'
        WHEN s.GENDER_CODE = 30 THEN 'FEMALE'
    END AS GENDER,
    ROUND(s.H_AVG, 1) AS H_AVG,
    ROUND(
        100.0 * COUNT(CASE WHEN s.CATEGORY_CODE = 10 AND s.AVERAGE_VALUE >= s.H_AVG THEN 1 END) 
        / COUNT(CASE WHEN s.CATEGORY_CODE = 10 THEN 1 END), 
    1) || '％' AS H_PER,
    ROUND(s.W_AVG, 1) AS W_AVG,
    ROUND(
        100.0 * COUNT(CASE WHEN s.CATEGORY_CODE = 20 AND s.AVERAGE_VALUE >= s.W_AVG THEN 1 END) 
        / COUNT(CASE WHEN s.CATEGORY_CODE = 20 THEN 1 END), 
    1) || '％' AS W_PER
FROM (
    SELECT 
        AGE, 
        GENDER_CODE, 
        CATEGORY_CODE,
        AVERAGE_VALUE,
        AVG(CASE WHEN CATEGORY_CODE = 10 THEN AVERAGE_VALUE ELSE NULL END) OVER (PARTITION BY AGE, GENDER_CODE) AS H_AVG,
        AVG(CASE WHEN CATEGORY_CODE = 20 THEN AVERAGE_VALUE ELSE NULL END) OVER (PARTITION BY AGE, GENDER_CODE) AS W_AVG
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
) s
GROUP BY s.AGE, s.GENDER_CODE, s.H_AVG, s.W_AVG
ORDER BY s.AGE DESC, s.GENDER_CODE DESC;