--各平均を求める
WITH AVERAGE AS(
SELECT
    CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
    ,ROUND(AVG(AVERAGE_VALUE),1) AS AVG
FROM
    SCHOOL_HEALTH
WHERE
    SURVEY_YEAR = '2019'
GROUP BY
    CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
)
--表の雛形を作成
,HW_AVG AS(
SELECT
    AGE
    ,GENDER_CODE
    ,SUM(CASE WHEN CATEGORY_CODE = '10' THEN AVG ELSE 0 END) AS H_AVG
    ,SUM(CASE WHEN CATEGORY_CODE = '20' THEN AVG ELSE 0  END) AS W_AVG
FROM
    AVERAGE
GROUP BY
    AGE
    ,GENDER_CODE
ORDER BY
    AGE DESC
    ,GENDER_CODE DESC
)
--身長に関して、平均以上の地区の割合を求める
,H_PER1 AS(
SELECT
    SH.AGE AS AGE
    ,SH.GENDER_CODE AS CODE
    ,ROUND((100.0*SUM(CASE WHEN SH.CATEGORY_CODE = 10 AND SH.AVERAGE_VALUE >= HW.H_AVG THEN 1 ELSE 0 END) / COUNT(*)),1) || '%' AS H_PER
FROM
    SCHOOL_HEALTH AS SH
JOIN
    HW_AVG AS HW
ON
    SH.AGE = HW.AGE
    AND SH.GENDER_CODE = HW.GENDER_CODE
WHERE
    SH.SURVEY_YEAR = '2019'
    AND SH.CATEGORY_CODE = '10'
GROUP BY
    SH.AGE
    ,SH.GENDER_CODE
)
--体重に関して、各項目の地区の総数を求める
,W_PER1 AS(
SELECT
    SH.AGE AS AGE
    ,SH.GENDER_CODE AS CODE
    ,ROUND((100.0*SUM(CASE WHEN SH.CATEGORY_CODE = 20 AND SH.AVERAGE_VALUE >= HW.W_AVG THEN 1 ELSE 0 END) / COUNT(*)),1) || '%' AS W_PER
FROM
    SCHOOL_HEALTH AS SH
JOIN
    HW_AVG AS HW
ON
    SH.AGE = HW.AGE
    AND SH.GENDER_CODE = HW.GENDER_CODE
WHERE
    SH.SURVEY_YEAR = '2019'
    AND SH.CATEGORY_CODE = '20'
GROUP BY
    SH.AGE
    ,SH.GENDER_CODE
)
SELECT
    HW.AGE
    ,CASE WHEN HW.GENDER_CODE = '20' THEN 'MALE' ELSE 'FEMALE' END AS GENDER
    ,HW.H_AVG
    ,H.H_PER
    ,HW.W_AVG
    ,W.W_PER
FROM
    HW_AVG AS HW
JOIN
    H_PER1 AS H
ON
    HW.AGE = H.AGE
    AND HW.GENDER_CODE = H.CODE
JOIN
    W_PER1 AS W
ON
    HW.AGE = W.AGE
    AND HW.GENDER_CODE = W.CODE
ORDER BY
    HW.AGE DESC
    ,HW.GENDER_CODE DESC;