-- 年齢・性別ごとに全地区の身長・体重の平均値を計算
WITH
    TMP_ALL AS (
        SELECT
            AGE,
            GENDER_CODE,
            ROUND(AVG(
                CASE
                    WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE
                    ELSE NULL
                END
            ), 1) AS H_AVG,
            ROUND(AVG(
                CASE
                    WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE
                    ELSE NULL
                END
            ), 1) AS W_AVG
        FROM SCHOOL_HEALTH
        WHERE SURVEY_YEAR = 2019
        GROUP BY AGE, GENDER_CODE
    )
-- AGE, GENDER ごとに
    -- H_AVG以上の平均身長である地区の割合
    -- W_AVG以上の平均体重である地区の割合
SELECT
    SH.AGE AS AGE,
    CASE
        WHEN SH.GENDER_CODE = '20' THEN 'MALE'
        WHEN SH.GENDER_CODE = '30' THEN 'FEMALE'
    END AS GENDER,
    TMP_ALL.H_AVG AS H_AVG,
    -- 平均身長が全体平均以上である地区の数 / 全地区数
    ROUND(
        100.0 * COUNT(
            CASE
                WHEN SH.CATEGORY_CODE = '10' AND SH.AVERAGE_VALUE >= TMP_ALL.H_AVG THEN 1
            END
        ) / COUNT(
            CASE
                WHEN SH.CATEGORY_CODE = '10' THEN 1
            END
        )
    , 1) || '%' AS H_PER,
    TMP_ALL.W_AVG AS W_AVG,
    -- 平均体重が全体平均以上である地区の数 / 全地区数
    ROUND(
        100.0 * COUNT(
            CASE
                WHEN SH.CATEGORY_CODE = '20' AND SH.AVERAGE_VALUE >= TMP_ALL.W_AVG THEN 1
            END
        ) / COUNT(
            CASE
                WHEN SH.CATEGORY_CODE = '20' THEN 1
            END
        )
    , 1) || '%' AS W_PER
FROM
    SCHOOL_HEALTH AS SH
LEFT JOIN
    TMP_ALL ON TMP_ALL.AGE = SH.AGE AND TMP_ALL.GENDER_CODE = SH.GENDER_CODE
WHERE
    SH.SURVEY_YEAR = 2019
GROUP BY
    SH.AGE,
    SH.GENDER_CODE
ORDER BY
    SH.AGE DESC,
    SH.GENDER_CODE DESC
;