WITH
TARGET AS (
    SELECT * FROM SCHOOL_HEALTH WHERE SURVEY_YEAR = 2019
),
HEIGHT AS (
    SELECT * FROM TARGET WHERE CATEGORY_CODE = '10'
),
WEIGHT AS (
    SELECT * FROM TARGET WHERE CATEGORY_CODE = '20'
),
HEIGHT_AVERAGE AS (
    SELECT
        AGE,
        GENDER_CODE,
        ROUND(SUM(AVERAGE_VALUE) / COUNT(AVERAGE_VALUE), 1) AS AVG
    FROM
        HEIGHT
    GROUP BY
        AGE,
        GENDER_CODE
),
WEIGHT_AVERAGE AS (
    SELECT
        AGE,
        GENDER_CODE,
        ROUND(SUM(AVERAGE_VALUE) / COUNT(AVERAGE_VALUE), 1)AS AVG
    FROM
        WEIGHT
    GROUP BY
        AGE,
        GENDER_CODE
),
HEIGHT_PERCENT AS (
    SELECT
        HEIGHT.AGE,
        HEIGHT.GENDER_CODE,
        PRINTF(ROUND(SUM(
            CASE WHEN HEIGHT.AVERAGE_VALUE >= HEIGHT_AVERAGE.AVG
            THEN 1.0
            ELSE 0.0
            END
        ) * 100 / COUNT(*), 1), '%4.1f') || '%' AS PER
    FROM
        HEIGHT
    INNER JOIN
        HEIGHT_AVERAGE On
        HEIGHT_AVERAGE.AGE = HEIGHT.AGE AND
        HEIGHT_AVERAGE.GENDER_CODE = HEIGHT.GENDER_CODE
    GROUP BY
        HEIGHT.AGE,
        HEIGHT.GENDER_CODE
),
WEIGHT_PERCENT AS (
    SELECT
        WEIGHT.AGE,
        WEIGHT.GENDER_CODE,
        PRINTF(ROUND(SUM(
            CASE WHEN WEIGHT.AVERAGE_VALUE >= WEIGHT_AVERAGE.AVG
            THEN 1.0
            ELSE 0.0
            END
        ) * 100 / COUNT(*), 1), '%4.1f') || '%' AS PER
    FROM
        WEIGHT
    INNER JOIN
        WEIGHT_AVERAGE On
        WEIGHT_AVERAGE.AGE = WEIGHT.AGE AND
        WEIGHT_AVERAGE.GENDER_CODE = WEIGHT.GENDER_CODE
    GROUP BY
        WEIGHT.AGE,
        WEIGHT.GENDER_CODE
)
SELECT
    TARGET.AGE,
    CASE TARGET.GENDER_CODE
    WHEN '20' THEN 'MALE'
    WHEN '30' THEN 'FEMALE'
    END AS GENDER,
    HEIGHT_AVERAGE.AVG AS H_AVG,
    HEIGHT_PERCENT.PER AS H_PER,
    WEIGHT_AVERAGE.AVG AS W_AVG,
    WEIGHT_PERCENT.PER AS W_PER
FROM (
    SELECT AGE, GENDER_CODE FROM TARGET GROUP BY AGE, GENDER_CODE
) AS
    TARGET
LEFT JOIN
    HEIGHT_AVERAGE ON
    HEIGHT_AVERAGE.AGE = TARGET.AGE AND
    HEIGHT_AVERAGE.GENDER_CODE = TARGET.GENDER_CODE
LEFT JOIN
    HEIGHT_PERCENT ON
    HEIGHT_PERCENT.AGE = TARGET.AGE AND
    HEIGHT_PERCENT.GENDER_CODE = TARGET.GENDER_CODE
LEFT JOIN
    WEIGHT_AVERAGE ON
    WEIGHT_AVERAGE.AGE = TARGET.AGE AND
    WEIGHT_AVERAGE.GENDER_CODE = TARGET.GENDER_CODE
LEFT JOIN
    WEIGHT_PERCENT ON
    WEIGHT_PERCENT.AGE = TARGET.AGE AND
    WEIGHT_PERCENT.GENDER_CODE = TARGET.GENDER_CODE
ORDER BY
    TARGET.AGE DESC,
    TARGET.GENDER_CODE DESC