WITH AVG_TABLE AS (
    SELECT
        AGE,
        GENDER_CODE,
        ROUND (
            AVG (
                CASE
                    WHEN CATEGORY_CODE = '10'
                    THEN AVERAGE_VALUE
                END
            )
            ,1
        ) AS H_AVG,
        ROUND (
            AVG (
                CASE
                    WHEN CATEGORY_CODE = '20'
                    THEN AVERAGE_VALUE
                END
            )
            ,1
        ) AS W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE,
        GENDER_CODE
)

SELECT
    SC.AGE AS AGE,
    CASE
        WHEN SC.GENDER_CODE = '20'
            THEN 'MALE'
        WHEN SC.GENDER_CODE = '30'
            THEN 'FEMALE'
    END AS GENDER,
    AV.H_AVG AS H_AVG,
    ROUND (
        100.0 * COUNT (
            CASE
                WHEN SC.AVERAGE_VALUE >= AV.H_AVG
                AND SC.CATEGORY_CODE = '10'
                THEN 1
            END
        ) /
        COUNT (
            CASE 
                WHEN SC.CATEGORY_CODE = '10'
                THEN 1
            END
        )
        ,1
    ) || '%' AS H_PER,
    AV.W_AVG AS W_AVG,
    ROUND (
        100.0 * COUNT (
            CASE
                WHEN SC.AVERAGE_VALUE >= AV.W_AVG
                AND SC.CATEGORY_CODE = '20'
                    THEN 1
            END
        ) /
        COUNT (
            CASE 
                WHEN SC.CATEGORY_CODE = '20'
                    THEN 1
            END
        )
        ,1
    ) || '%' AS W_PER
FROM
    SCHOOL_HEALTH AS SC
LEFT JOIN
    AVG_TABLE AS AV
ON
    SC.AGE = AV.AGE
    AND SC.GENDER_CODE = AV.GENDER_CODE
WHERE
    SC.SURVEY_YEAR = 2019
GROUP BY
    SC.AGE,
    SC.GENDER_CODE
ORDER BY
    SC.AGE DESC,
    SC.GENDER_CODE DESC