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