SELECT
    AGE,
    CASE WHEN GENDER_CODE = 20 THEN 'MALE' ELSE 'FEMALE' END AS GENDER,
    MAX(CASE WHEN CATEGORY_CODE = 10 THEN AVG_VALUE END) AS H_AVG,
    ROUND(
        CAST(SUM(CASE WHEN CATEGORY_CODE = 10 AND AVERAGE_VALUE >= AVG_VALUE THEN 1 END) AS REAL)
        / SUM(CASE WHEN CATEGORY_CODE = 10 THEN 1 END)
        * 100.0,
        1
    ) || '%' AS H_PER,
    MAX(CASE WHEN CATEGORY_CODE = 20 THEN AVG_VALUE END) AS W_AVG,
    ROUND(
        CAST(SUM(CASE WHEN CATEGORY_CODE = 20 AND AVERAGE_VALUE >= AVG_VALUE THEN 1 END) AS REAL)
        / SUM(CASE WHEN CATEGORY_CODE = 20 THEN 1 END)
        * 100.0,
        1
    ) || '%' AS W_PER
FROM
    SCHOOL_HEALTH
INNER JOIN (
    SELECT
        SURVEY_YEAR,
        CATEGORY_CODE,
        GENDER_CODE,
        AGE,
        ROUND(AVG(AVERAGE_VALUE), 1) AS AVG_VALUE
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        CATEGORY_CODE,
        GENDER_CODE,
        AGE
) USING (SURVEY_YEAR, CATEGORY_CODE, GENDER_CODE, AGE)
GROUP BY GENDER_CODE, AGE
ORDER BY
    AGE DESC,
    GENDER_CODE DESC
;