WITH AvgValues AS (
    -- 各カテゴリコード(身長・体重)ごとの全体平均値を算出
    SELECT 
        AGE,
        GENDER_CODE,
        CATEGORY_CODE,
        ROUND(AVG(AVERAGE_VALUE), 1) AS AVERAGE
    FROM 
        SCHOOL_HEALTH
    WHERE 
        SURVEY_YEAR = 2019
    GROUP BY 
        AGE, GENDER_CODE, CATEGORY_CODE
),

HeightAbove AS (
    -- 身長の平均値以上の地区の割合を算出
    SELECT 
        sh.AGE,
        sh.GENDER_CODE,
        ROUND(100.0 * SUM(CASE WHEN sh.CATEGORY_CODE = '10' AND sh.AVERAGE_VALUE >= av.AVERAGE THEN 1 ELSE 0 END) / COUNT(*), 1) AS H_PER
    FROM 
        SCHOOL_HEALTH sh
    JOIN 
        AvgValues av ON sh.AGE = av.AGE AND sh.GENDER_CODE = av.GENDER_CODE AND av.CATEGORY_CODE = '10'
    WHERE 
        sh.SURVEY_YEAR = 2019 AND sh.CATEGORY_CODE = '10'
    GROUP BY 
        sh.AGE, sh.GENDER_CODE
),

WeightAbove AS (
    -- 体重の平均値以上の地区の割合を算出
    SELECT 
        sh.AGE,
        sh.GENDER_CODE,
        ROUND(100.0 * SUM(CASE WHEN sh.CATEGORY_CODE = '20' AND sh.AVERAGE_VALUE >= av.AVERAGE THEN 1 ELSE 0 END) / COUNT(*), 1) AS W_PER
    FROM 
        SCHOOL_HEALTH sh
    JOIN 
        AvgValues av ON sh.AGE = av.AGE AND sh.GENDER_CODE = av.GENDER_CODE AND av.CATEGORY_CODE = '20'
    WHERE 
        sh.SURVEY_YEAR = 2019 AND sh.CATEGORY_CODE = '20'
    GROUP BY 
        sh.AGE, sh.GENDER_CODE
)

-- 最終結果の選択
SELECT 
    avg_age.AGE,
    CASE avg_age.GENDER_CODE WHEN '20' THEN 'MALE' ELSE 'FEMALE' END AS GENDER,
    avg_case.AVERAGE AS H_AVG,
    ha.H_PER || '％' AS H_PER,
    avg_weight.AVERAGE AS W_AVG,
    wa.W_PER || '％' AS W_PER
FROM 
    (SELECT DISTINCT AGE, GENDER_CODE FROM SCHOOL_HEALTH WHERE SURVEY_YEAR = 2019) avg_age
LEFT JOIN 
    AvgValues avg_case ON avg_age.AGE = avg_case.AGE AND avg_age.GENDER_CODE = avg_case.GENDER_CODE AND avg_case.CATEGORY_CODE = '10'
LEFT JOIN 
    AvgValues avg_weight ON avg_age.AGE = avg_weight.AGE AND avg_age.GENDER_CODE = avg_weight.GENDER_CODE AND avg_weight.CATEGORY_CODE = '20'
LEFT JOIN 
    HeightAbove ha ON avg_age.AGE = ha.AGE AND avg_age.GENDER_CODE = ha.GENDER_CODE
LEFT JOIN 
    WeightAbove wa ON avg_age.AGE = wa.AGE AND avg_age.GENDER_CODE = wa.GENDER_CODE
ORDER BY 
    avg_age.AGE DESC, avg_age.GENDER_CODE DESC;