SELECT 
    s.AGE,
    CASE 
        WHEN s.GENDER_CODE = 20 THEN 'MALE'
        WHEN s.GENDER_CODE = 30 THEN 'FEMALE'
    END AS GENDER,
    s.H_AVG,
    COUNT(CASE WHEN s.CATEGORY_CODE = 10 AND s.AVERAGE_VALUE >= s.H_AVG THEN 1 END) / COUNT(*) * 100 AS H_PER,
    s.W_AVG,
    COUNT(CASE WHEN s.CATEGORY_CODE = 20 AND s.AVERAGE_VALUE >= s.W_AVG THEN 1 END) / COUNT(*) * 100 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 SURVEY
    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;