--GENDER：性別振り分け
--SURVEY_YEAR：2019年に絞り込み
--CATEGORY_CODE:身長体重振り分け
WITH A AS(
SELECT *,
(CASE GENDER_CODE
WHEN '20' THEN 'MALE'
WHEN '30' THEN 'FEMALE'
ELSE 0 END) AS GENDER
FROM SCHOOL_HEALTH
WHERE SURVEY_YEAR = 2019
ORDER BY PF_CODE,GENDER_CODE DESC,AGE DESC
),

--身長平均
B AS(
SELECT AGE,GENDER,GENDER_CODE,
ROUND(AVG(AVERAGE_VALUE), 1) AS H_AVG,
COUNT(PF_CODE) AS CNT
FROM A
WHERE CATEGORY_CODE = 10
GROUP BY AGE,GENDER
ORDER BY AGE DESC,GENDER_CODE DESC
),

--平均値以上の地区の割合を表示
C AS(
SELECT B.AGE,B.GENDER,B.H_AVG,B.CNT,
COUNT(PF_CODE) AS CNT2,
ROUND(COUNT(PF_CODE)*1.00 / B.CNT*1.00 *100,1) || "%" AS H_PER
FROM B
JOIN A
ON B.AGE = A.AGE
  AND B.GENDER = A.GENDER
WHERE AVERAGE_VALUE >= B.H_AVG
  AND CATEGORY_CODE = 10
GROUP BY B.AGE,B.GENDER
ORDER BY B.AGE DESC,B.GENDER_CODE DESC
),

--体重平均
D AS(
SELECT AGE,GENDER,GENDER_CODE,
ROUND(AVG(AVERAGE_VALUE), 1) AS W_AVG,
COUNT(PF_CODE) AS CNT3
FROM A
WHERE CATEGORY_CODE = 20
GROUP BY AGE,GENDER
ORDER BY AGE DESC,GENDER_CODE DESC
),

--平均値以上の地区の割合を表示
E AS(
SELECT D.AGE,D.GENDER,D.W_AVG,D.CNT3,
COUNT(PF_CODE) AS CNT4,
ROUND(COUNT(PF_CODE)*1.00 / D.CNT3*1.00 *100,1) || "%" AS W_PER
FROM D
JOIN A
ON D.AGE = A.AGE
  AND D.GENDER = A.GENDER
WHERE AVERAGE_VALUE >= D.W_AVG
  AND CATEGORY_CODE = 20
GROUP BY D.AGE,D.GENDER
ORDER BY D.AGE DESC,D.GENDER_CODE DESC
),

--合算
F AS(
SELECT C.AGE,C.GENDER,
       C.H_AVG,C.H_PER,
       E.W_AVG,E.W_PER
FROM C
JOIN E
ON C.AGE = E.AGE
  AND C.GENDER = E.GENDER
ORDER BY C.AGE DESC
)


SELECT *
FROM F;