-- 再帰 WITH 読みにくい、もっといい解法ありそう。
-- ALL_AVG でカテゴリー、性別、年齢ごとの平均を計算する。
-- PF_AVG で都道府県、カテゴリー、性別、年齢ごとの平均を計算する。ALL_AVG の結果も持っておく。
-- STATS でカテゴリー、性別、年齢ごとの割合を PF_AVG の結果をもとに計算する。
-- コードテストでなぜか H_PER と W_AVG の順番が入れ替わる。
WITH STATS AS (
    WITH PF_AVG AS (
        WITH ALL_AVG AS (
            SELECT
                CATEGORY_CODE, GENDER_CODE, AGE, ROUND(AVG(AVERAGE_VALUE), 1) AS ALL_AVERAGE
            FROM
                SCHOOL_HEALTH
            WHERE
                SURVEY_YEAR = 2019
            GROUP BY
                1, 2, 3
        )
        SELECT
            PF_CODE,
            S.CATEGORY_CODE,
            S.GENDER_CODE,
            S.AGE,
            AVG(AVERAGE_VALUE) AS PF_AVERAGE,
            ALL_AVERAGE
        FROM
            SCHOOL_HEALTH AS S
        INNER JOIN
            ALL_AVG
        ON
            S.CATEGORY_CODE = ALL_AVG.CATEGORY_CODE AND
            S.GENDER_CODE = ALL_AVG.GENDER_CODE AND
            S.AGE = ALL_AVG.AGE
        WHERE
            SURVEY_YEAR = 2019
        GROUP BY
            1, 2, 3, 4
    )
    SELECT
        CATEGORY_CODE, GENDER_CODE, AGE, ALL_AVERAGE,
        ROUND(SUM(CASE WHEN PF_AVERAGE >= ALL_AVERAGE THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) || '%' AS PER
        -- 文字列の連結は || でおこなう。
        -- C++ と同じように 1.0 を掛けることで小数にキャストできる。
    FROM
        PF_AVG
    GROUP BY
        1, 2, 3
)
SELECT
    H.AGE AS 'AGE',
    CASE WHEN H.GENDER_CODE = '20' THEN 'MALE' ELSE 'FEMALE' END AS 'GENDER',
    H.ALL_AVERAGE AS 'H_AVG',
    H.PER AS 'H_PER',
    W.ALL_AVERAGE AS 'W_AVG',
    W.PER AS 'W_PER'
FROM
    STATS AS H
INNER JOIN
    STATS AS W
ON
    H.AGE = W.AGE AND
    H.GENDER_CODE = W.GENDER_CODE
WHERE
    H.CATEGORY_CODE = '10' AND
    W.CATEGORY_CODE = '20'
ORDER BY
    1 DESC, 2