/*
地区毎の学校の保険データを集計した学校保健テーブル(SCHOOL_HEALTH)より、調査年(SURVEY_YEAR)が2019年のデータを年齢・性別ごとに身長、体重それぞれの
平均値を算出して四捨五入した値が、平均値以上の地区の割合を表示しなさい。 
地区毎の集計人数は同じであるとし、各地区の平均値から全体の平均値をそのまま算出してよいものとする。
割合はパーセント表示にして、値の後に'％'を付加して表示すること。
カテゴリコード(CATEGORY_CODE)には、'10'(身長)、'20'(体重)がセットされており、性別コード(GENDER_CODE)には、'20'(男)、'30'(女)がセットされている。
また、平均値、割合の計算結果は小数点第2位を四捨五入すること。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)

AGE → AGE
GENDER_CODE('20'は 'MALE' 、'30'は 'FEMALE' と表示する) → GENDER
身長の平均値 → H_AVG
身長平均値以上の割合 → H_PER
体重の平均値 → W_AVG
体重平均値以上の割合 → W_PER
表示順

AGEの降順
GENDER_CODEの降順


*/
WITH HEIGHT AS (
    SELECT A.GENDER_CODE
          ,A.AGE
          ,A.CATEGORY_CODE
          ,COUNT(*) AS HEIGHT_CNT
          ,ROUND(AVG(A.AVERAGE_VALUE), 1) AS HEIGHT_AVG
    FROM SCHOOL_HEALTH A
    WHERE A.SURVEY_YEAR = 2019
    AND A.CATEGORY_CODE = '10'
    GROUP BY A.GENDER_CODE, A.AGE, A.CATEGORY_CODE
)
,WEIGHT AS (
    SELECT A.GENDER_CODE
          ,A.AGE
          ,A.CATEGORY_CODE
          ,COUNT(*) AS WEIGHT_CNT
          ,ROUND(AVG(A.AVERAGE_VALUE), 1) AS WEIGHT_AVG
    FROM SCHOOL_HEALTH A
    WHERE A.SURVEY_YEAR = 2019
    AND A.CATEGORY_CODE = '20'
    GROUP BY A.GENDER_CODE, A.AGE, A.CATEGORY_CODE
)
SELECT WK1.AGE AS AGE
      ,CASE WK1.GENDER_CODE
           WHEN '20' THEN 'MALE'
           ELSE 'FEMALE'
       END AS GENDER
      ,WK2.HEIGHT_AVG AS H_AVG
      ,WK2.HEIGHT_PER || '%' AS H_PER
      ,WK3.WEIGHT_AVG AS W_AVG
      ,WK3.WEIGHT_PER || '%' AS W_PER
FROM (
    SELECT A.AGE
          ,A.GENDER_CODE
    FROM SCHOOL_HEALTH A
    GROUP BY A.AGE, A.GENDER_CODE
) WK1
INNER JOIN (
    SELECT A.AGE
          ,A.GENDER_CODE
          ,B.HEIGHT_AVG
          ,FORMAT(ROUND(CAST((COUNT(*) * 100) AS REAL) / CAST(B.HEIGHT_CNT AS REAL), 1), '000.0') AS HEIGHT_PER
    FROM SCHOOL_HEALTH A
    INNER JOIN HEIGHT B
    ON A.AGE = B.AGE
    AND A.GENDER_CODE = B.GENDER_CODE
    WHERE A.CATEGORY_CODE = '10'
    AND A.AVERAGE_VALUE >= B.HEIGHT_AVG
    AND A.SURVEY_YEAR = 2019
    GROUP BY A.AGE, A.GENDER_CODE
) WK2
ON WK1.AGE = WK2.AGE
AND WK1.GENDER_CODE = WK2.GENDER_CODE
INNER JOIN (
    SELECT A.AGE
          ,A.GENDER_CODE
          ,B.WEIGHT_AVG
          ,FORMAT(ROUND(CAST((COUNT(*) * 100) AS REAL) / CAST(B.WEIGHT_CNT AS REAL), 1), '000.0') AS WEIGHT_PER
    FROM SCHOOL_HEALTH A
    INNER JOIN WEIGHT B
    ON A.AGE = B.AGE
    AND A.GENDER_CODE = B.GENDER_CODE
    WHERE A.CATEGORY_CODE = '20'
    AND A.AVERAGE_VALUE >= B.WEIGHT_AVG
    AND A.SURVEY_YEAR = 2019
    GROUP BY A.AGE, A.GENDER_CODE
) WK3
ON WK1.AGE = WK3.AGE
AND WK1.GENDER_CODE = WK3.GENDER_CODE
ORDER BY WK1.AGE DESC, WK1.GENDER_CODE DESC