WITH AvgData AS (
    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
)

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 AvgData AS s
GROUP BY s.AGE, s.GENDER_CODE
ORDER BY s.AGE DESC, s.GENDER_CODE DESC;