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
)
, HeightAboveAverage AS (
    SELECT 
        AGE,
        GENDER_CODE,
        COUNT(*) AS H_ABOVE_COUNT
    FROM AvgData
    WHERE CATEGORY_CODE = '10' AND AVERAGE_VALUE >= H_AVG
    GROUP BY AGE, GENDER_CODE
)
, WeightAboveAverage AS (
    SELECT 
        AGE,
        GENDER_CODE,
        COUNT(*) AS W_ABOVE_COUNT
    FROM AvgData
    WHERE CATEGORY_CODE = '20' AND AVERAGE_VALUE >= W_AVG
    GROUP BY AGE, GENDER_CODE
)

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 * COALESCE(h.H_ABOVE_COUNT, 0) / 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 * COALESCE(w.W_ABOVE_COUNT, 0) / COUNT(CASE WHEN s.CATEGORY_CODE = '20' THEN 1 END), 1) || '％' AS W_PER
FROM AvgData AS s
LEFT JOIN HeightAboveAverage AS h ON s.AGE = h.AGE AND s.GENDER_CODE = h.GENDER_CODE
LEFT JOIN WeightAboveAverage AS w ON s.AGE = w.AGE AND s.GENDER_CODE = w.GENDER_CODE
GROUP BY s.AGE, s.GENDER_CODE, s.H_AVG, s.W_AVG, h.H_ABOVE_COUNT, w.W_ABOVE_COUNT
ORDER BY s.AGE DESC, s.GENDER_CODE DESC;