WITH AvgData AS (
    SELECT 
        AGE,
        GENDER_CODE,
        CATEGORY_CODE,
        ROUND(AVG(AVERAGE_VALUE), 1) AS AVG_VALUE
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    GROUP BY AGE, GENDER_CODE, CATEGORY_CODE
)
, HeightAboveAverage AS (
    SELECT 
        AGE,
        GENDER_CODE,
        COUNT(*) AS H_ABOVE_COUNT
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019 AND CATEGORY_CODE = '10'
    GROUP BY AGE, GENDER_CODE
    HAVING AVG(AVERAGE_VALUE) >= (SELECT AVG_VALUE FROM AvgData WHERE AGE = SCHOOL_HEALTH.AGE AND GENDER_CODE = SCHOOL_HEALTH.GENDER_CODE AND CATEGORY_CODE = '10')
)
, WeightAboveAverage AS (
    SELECT 
        AGE,
        GENDER_CODE,
        COUNT(*) AS W_ABOVE_COUNT
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019 AND CATEGORY_CODE = '20'
    GROUP BY AGE, GENDER_CODE
    HAVING AVG(AVERAGE_VALUE) >= (SELECT AVG_VALUE FROM AvgData WHERE AGE = SCHOOL_HEALTH.AGE AND GENDER_CODE = SCHOOL_HEALTH.GENDER_CODE AND CATEGORY_CODE = '20')
)

SELECT 
    a.AGE AS AGE,
    CASE 
        WHEN a.GENDER_CODE = '20' THEN 'MALE'
        WHEN a.GENDER_CODE = '30' THEN 'FEMALE'
    END AS GENDER,
    CASE 
        WHEN a.CATEGORY_CODE = '10' THEN AVG_VALUE
        ELSE NULL
    END AS H_AVG,
    ROUND(100.0 * COALESCE(h.H_ABOVE_COUNT, 0) / COUNT(CASE WHEN a.CATEGORY_CODE = '10' THEN 1 END), 1) || '％' AS H_PER,
    CASE 
        WHEN a.CATEGORY_CODE = '20' THEN AVG_VALUE
        ELSE NULL
    END AS W_AVG,
    ROUND(100.0 * COALESCE(w.W_ABOVE_COUNT, 0) / COUNT(CASE WHEN a.CATEGORY_CODE = '20' THEN 1 END), 1) || '％' AS W_PER
FROM AvgData AS a
LEFT JOIN HeightAboveAverage AS h ON a.AGE = h.AGE AND a.GENDER_CODE = h.GENDER_CODE
LEFT JOIN WeightAboveAverage AS w ON a.AGE = w.AGE AND a.GENDER_CODE = w.GENDER_CODE
GROUP BY a.AGE, a.GENDER_CODE, a.CATEGORY_CODE, a.AVG_VALUE, h.H_ABOVE_COUNT, w.W_ABOVE_COUNT
ORDER BY a.AGE DESC, a.GENDER_CODE DESC;