WITH AVE AS (
    SELECT
        AGE,
        GENDER_CODE,
        ROUND(
            AVG(
                CASE
                    WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE
                END
            ),
            1
        ) H_AVG,
        ROUND(
            AVG(
                CASE
                    WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE
                END
            ),
            1
        ) W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = '2019'
    GROUP BY
        AGE,
        GENDER_CODE
),
Y_2019 AS(
    SELECT
        *
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = '2019'
)
SELECT
    AGE,
    CASE
        WHEN GENDER_CODE = '20' THEN 'MALE'
        WHEN GENDER_CODE = '30' THEN 'FEMALE'
    END GENDER,
    H_AVG,
    ROUND(
        100.0 * COUNT(
            CASE
                WHEN CATEGORY_CODE = '10'
                AND AVERAGE_VALUE >= H_AVG THEN 1
            END
        ) / COUNT(
            CASE
                WHEN CATEGORY_CODE = '10' THEN 1
            END
        ),
        1
    ) || '%' H_PER,
    W_AVG,
    ROUND(
        100.0 * COUNT(
            CASE
                WHEN CATEGORY_CODE = '20'
                AND AVERAGE_VALUE >= W_AVG THEN 1
            END
        ) / COUNT(
            CASE
                WHEN CATEGORY_CODE = '20' THEN 1
            END
        ),
        1
    ) || '%' W_PER
FROM
    Y_2019
    INNER JOIN AVE USING(AGE, GENDER_CODE)
GROUP BY
    GENDER_CODE,
    AGE
ORDER BY
    AGE DESC,
    GENDER_CODE DESC