WITH AvgValues AS (
    SELECT
        AGE,
        GENDER_CODE,
        AVG(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE END) AS H_AVG,
        AVG(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE END) AS W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE,
        GENDER_CODE
)
SELECT
    A.AGE,
    CASE A.GENDER_CODE
        WHEN '20' THEN 'MALE'
        WHEN '30' THEN 'FEMALE'
    END AS GENDER,
    ROUND(A.H_AVG, 2) AS H_AVG,
    ROUND(A.W_AVG, 2) AS W_AVG,
    ROUND(SUM(CASE WHEN B.CATEGORY_CODE = '10' AND B.AVERAGE_VALUE >= A.H_AVG THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS H_PER,
    ROUND(SUM(CASE WHEN B.CATEGORY_CODE = '20' AND B.AVERAGE_VALUE >= A.W_AVG THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS W_PER
FROM
    AvgValues A
JOIN
    SCHOOL_HEALTH B ON A.AGE = B.AGE AND A.GENDER_CODE = B.GENDER_CODE
WHERE
    B.SURVEY_YEAR = 2019
GROUP BY
    A.AGE,
    A.GENDER_CODE
ORDER BY
    A.AGE DESC,
    A.GENDER_CODE DESC;