WITH TH AS(
    SELECT
        AGE AS AGE,
        GENDER_CODE AS GENDER,
        ROUND(AVG(AVERAGE_VALUE), 1) AS H_AVG,
        COUNT(*) AS H_CNT
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
        AND CATEGORY_CODE = 10
    GROUP BY
        AGE,
        GENDER_CODE
),
TW AS(
    SELECT
        AGE AS AGE,
        GENDER_CODE AS GENDER,
        ROUND(AVG(AVERAGE_VALUE), 1) AS W_AVG,
        COUNT(*) AS W_CNT
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
        AND CATEGORY_CODE = 20
    GROUP BY
        AGE,
        GENDER_CODE
)
SELECT
    SCHOOL_HEALTH.AGE AS AGE,
    CASE SCHOOL_HEALTH.GENDER_CODE
        WHEN 20 THEN 'MALE'
        WHEN 30 THEN 'FEMALE'
        ELSE 'ERROR'
    END AS GENDER,
    ROUND(H_AVG, 1) AS H_AVG,
    ROUND(
        SUM(
            CASE
                WHEN CATEGORY_CODE = 10 AND AVERAGE_VALUE >= H_AVG THEN 100.0 / H_CNT
                ELSE 0
            END
        ), 1
    ) || '%' AS H_PER,
    ROUND(W_AVG, 1) AS W_AVG,
    ROUND(
        SUM(
            CASE
                WHEN CATEGORY_CODE = 20 AND AVERAGE_VALUE >= W_AVG THEN 100.0 / W_CNT
                ELSE 0
            END
        ), 1
    ) || '%' AS W_PER
FROM
    SCHOOL_HEALTH
    INNER JOIN
        TH ON SCHOOL_HEALTH.AGE = TH.AGE AND SCHOOL_HEALTH.GENDER_CODE = TH.GENDER,
        TW ON SCHOOL_HEALTH.AGE = TW.AGE AND SCHOOL_HEALTH.GENDER_CODE = TW.GENDER
WHERE
    SURVEY_YEAR = 2019
GROUP BY
    SCHOOL_HEALTH.AGE,
    SCHOOL_HEALTH.GENDER_CODE
ORDER BY
    AGE DESC,
    SCHOOL_HEALTH.GENDER_CODE DESC