WITH HEIGHT_AVG AS (
    SELECT
        AGE
        , ROUND(
            SUM(CASE CATEGORY_CODE
                WHEN '10' THEN AVERAGE_VALUE
                ELSE 0
                END) / 
            SUM(CASE CATEGORY_CODE
                WHEN '10' THEN 1
                ELSE 0
                END)
        , 1)
    AS 
        H_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE
        , GENDER_CODE
),
HEIGHT_RATE AS (
    SELECT
        AGE
        , COUNT(AVERAGE_VALUE > HEIGHT_AVG.H_AVG) / COUNT(*) AS H_PER
        FROM
        SCHOOL_HEALTH
        JOIN
    HEIGHT_RATE
ON
    HEIGHT_RATE.AGE = HEIGHT_AVG.AGE
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE
        , GENDER_CODE
),
WEIGHT_AVG AS (
    SELECT
        AGE
        , ROUND(
            SUM(CASE CATEGORY_CODE
                WHEN '20' THEN AVERAGE_VALUE
                ELSE 0
                END) / 
            SUM(CASE CATEGORY_CODE
                WHEN '20' THEN 1
                ELSE 0
                END)
        , 1)
    AS 
        W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE
        , GENDER_CODE
),
WEIGHT_RATE AS (
    SELECT
        AGE
        , COUNT(AVERAGE_VALUE >  WEIGHT_AVG.W_AVG) / COUNT(*) AS H_PER
        FROM
        SCHOOL_HEALTH
        JOIN
    WEIGHT_RATE
ON
    WEIGHT_RATE.AGE = WEIGHT_AVG.AGE
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE
        , GENDER_CODE
)
SELECT 
    SCHOOL_HEALTH.AGE
    , CASE GENDER_CODE
        WHEN '20' THEN 'MALE'
        ELSE 'FEMALE'
        END AS GENDER
FROM
    SCHOOL_HEALTH
JOIN
    HEIGHT_RATE
ON
    SCHOOL_HEALTH.AGE = HEIGHT_RATE.AGE
JOIN
    WEIGHT_RATE
ON
    WEIGHT_RATE.AGE = SCHOOL_HEALTH.AGE

WHERE
    SURVEY_YEAR = 2019
GROUP BY
    SCHOOL_HEALTH.AGE
    , GENDER
ORDER BY 
    SCHOOL_HEALTH.AGE DESC
    , GENDER_CODE DESC;