WITH
    AVE_TBE AS (
        SELECT
            AGE,
            GENDER_CODE,
            ROUND(
                AVG(
                    CASE CATEGORY_CODE
                        WHEN 10 THEN AVERAGE_VALUE
                    END
                ),
                1
            ) AS H_AVE,
            ROUND(
                AVG(
                    CASE CATEGORY_CODE
                        WHEN 20 THEN AVERAGE_VALUE
                    END
                ),
                1
            ) AS W_AVE
        FROM
            SCHOOL_HEALTH
        WHERE
            SURVEY_YEAR = 2019
        GROUP BY
            AGE,
            GENDER_CODE
    )
SELECT
    ave.AGE AS AGE,
    CASE ave.GENDER_CODE
        WHEN 20 THEN 'MALE'
        ELSE 'FEMALE'
    END AS GENDER,
    MAX(ave.H_AVE) AS H_AVG,
    ROUND(
        100.0 * COUNT(
            CASE
                WHEN sh.CATEGORY_CODE = 10
                AND sh.AVERAGE_VALUE >= ave.H_AVE THEN 1
            END
        ) / COUNT(
            CASE
                WHEN sh.CATEGORY_CODE = 10 THEN 1
            END
        ),
        1
    )  || '%' AS H_PER,
    MAX(ave.W_AVE) AS W_AVG,
    ROUND(
        100.0 * COUNT(
            CASE
                WHEN sh.CATEGORY_CODE = 20
                AND sh.AVERAGE_VALUE >= ave.W_AVE THEN 1
            END
        ) / COUNT(
            CASE
                WHEN sh.CATEGORY_CODE = 20 THEN 1
            END
        ),
        1
    )  || '%' AS W_PER
FROM
    AVE_TBE AS ave
    LEFT OUTER JOIN SCHOOL_HEALTH AS sh ON ave.AGE = sh.AGE
    AND ave.GENDER_CODE = sh.GENDER_CODE
    AND sh.SURVEY_YEAR = 2019
GROUP BY
    ave.AGE,
    ave.GENDER_CODE
ORDER BY
    ave.AGE DESC,
    ave.GENDER_CODE DESC;