WITH subq AS (
--2019年のデータを年齢・性別ごとに身長体重の平均値を算出
    SELECT
        AGE
        ,GENDER_CODE
        ,ROUND(
           AVG(CASE WHEN CATEGORY_CODE = '10'
                    THEN AVERAGE_VALUE
                    END)
              , 1) AS H_AVG
        ,ROUND(
           AVG(CASE WHEN CATEGORY_CODE = '20'
                    THEN AVERAGE_VALUE
                    END)
              , 1) AS W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE
        ,GENDER_CODE
    )
    
SELECT
    subq.AGE
    --MALE,FEMALEの書き分け
    ,CASE WHEN subq.GENDER_CODE = '20'
          THEN 'MALE'
          WHEN subq.GENDER_CODE = '30'
          THEN 'FEMALE'
          END AS GENDER
    ,MAX(H_AVG)
    ,ROUND(
        100.0 * COUNT(
            CASE WHEN SH.CATEGORY_CODE = 10
                   AND H_AVG <= SH.AVERAGE_VALUE
                 THEN 1
                 END) / COUNT(CASE WHEN SH.CATEGORY_CODE = 10
                                   THEN 1 
                                   END), 1
                                   ) || '%' AS H_PER
    ,MAX(W_AVG)
    ,ROUND(
        100.0 * COUNT(
            CASE WHEN SH.CATEGORY_CODE = 20
                   AND W_AVG <= SH.AVERAGE_VALUE
                 THEN 1
                 END) / COUNT(CASE WHEN SH.CATEGORY_CODE = 20
                                   THEN 1 
                                   END), 1
                                   ) || '%'  AS W_PER
FROM
    subq
LEFT OUTER JOIN
    SCHOOL_HEALTH AS SH
  ON
    subq.AGE = SH.AGE
    AND subq.GENDER_CODE = SH.GENDER_CODE
    AND SURVEY_YEAR = 2019
GROUP BY
    subq.AGE
    ,subq.GENDER_CODE
ORDER BY
    subq.AGE DESC
    ,subq.GENDER_CODE DESC;