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

WITH h_avg as(
    SELECT
        AGE,
        GENDER_CODE,
        COUNT(*) AS "COUNT",
        ROUND(SUM(AVERAGE_VALUE) / COUNT(*), 1) AS "H_AVG"
    FROM SCHOOL_HEALTH
    WHERE 
        SURVEY_YEAR = '2019'
        AND
        CATEGORY_CODE = '10'
    GROUP BY AGE, GENDER_CODE
    ORDER BY AGE, GENDER_CODE
),
h as(
    SELECT
        SCHOOL_HEALTH.AGE,
        SCHOOL_HEALTH.GENDER_CODE,
        H_AVG,
        ROUND(CAST(COUNT(*) * 100 as real)  / cast(COUNT as real), 1) || '%' AS "H_PER"
    FROM SCHOOL_HEALTH
    JOIN h_avg ON SCHOOL_HEALTH.AGE = h_avg.AGE
              AND SCHOOL_HEALTH.GENDER_CODE = h_avg.GENDER_CODE
    WHERE 
        SURVEY_YEAR = '2019'
        AND
        CATEGORY_CODE = '10'
        AND
        h_avg.H_AVG <= AVERAGE_VALUE
    GROUP BY SCHOOL_HEALTH.AGE, SCHOOL_HEALTH.GENDER_CODE
    ORDER BY SCHOOL_HEALTH.AGE, SCHOOL_HEALTH.GENDER_CODE
),
w_avg as(
    SELECT
        AGE,
        GENDER_CODE,
        COUNT(*) AS "COUNT",
        ROUND(SUM(AVERAGE_VALUE) / COUNT(*), 1) AS "W_AVG"
    FROM SCHOOL_HEALTH
    WHERE 
        SURVEY_YEAR = '2019'
        AND
        CATEGORY_CODE = '20'
    GROUP BY AGE, GENDER_CODE
    ORDER BY AGE, GENDER_CODE
),
w as(
    SELECT
        SCHOOL_HEALTH.AGE,
        SCHOOL_HEALTH.GENDER_CODE,
        W_AVG,
        ROUND(CAST(COUNT(*) * 100 as real)  / cast(COUNT as real), 1) || '%' AS "W_PER"
    FROM SCHOOL_HEALTH
    JOIN w_avg ON SCHOOL_HEALTH.AGE = w_avg.AGE
              AND SCHOOL_HEALTH.GENDER_CODE = w_avg.GENDER_CODE
    WHERE 
        SURVEY_YEAR = '2019'
        AND
        CATEGORY_CODE = '20'
        AND
        w_avg.W_AVG <= AVERAGE_VALUE
    GROUP BY SCHOOL_HEALTH.AGE, SCHOOL_HEALTH.GENDER_CODE
    ORDER BY SCHOOL_HEALTH.AGE, SCHOOL_HEALTH.GENDER_CODE
)
SELECT DISTINCT
    SCHOOL_HEALTH.AGE AS "AGE",
    CASE
        WHEN SCHOOL_HEALTH.GENDER_CODE = '20' THEN 'MALE'
        WHEN SCHOOL_HEALTH.GENDER_CODE = '30' THEN 'FEMALE'
    END AS "GENDER",
    h.H_AVG AS "H_AVG",
    h.H_PER AS "H_PER",
    w.W_AVG AS "W_AVG",
    w.W_PER AS "W_PER"
FROM SCHOOL_HEALTH
JOIN h ON SCHOOL_HEALTH.AGE = h.AGE
      AND SCHOOL_HEALTH.GENDER_CODE = h.GENDER_CODE
JOIN w ON SCHOOL_HEALTH.AGE = w.AGE
      AND SCHOOL_HEALTH.GENDER_CODE = w.GENDER_CODE
ORDER BY
    SCHOOL_HEALTH.AGE DESC, SCHOOL_HEALTH.GENDER_CODE DESC