--AGE → AGE
--GENDER_CODE('20'は 'MALE' 、'30'は 'FEMALE' と表示する) → GENDER
--身長の平均値 → H_AVG
--身長平均値以上の割合 → H_PER
--体重の平均値 → W_AVG
--体重平均値以上の割合 → W_PER
WITH ha AS (
SELECT
AGE AS AGE
,GENDER_CODE
,CASE WHEN GENDER_CODE = '20' THEN 'MALE' 
      WHEN GENDER_CODE = '30' THEN 'FEMALE' ELSE '' END AS GENDER
,
ROUND(AVG(AVERAGE_VALUE),0) AS H_AVG
FROM SCHOOL_HEALTH
WHERE SURVEY_YEAR = '2019'
AND CATEGORY_CODE = '10'
GROUP BY
AGE
,GENDER_CODE
,CASE WHEN GENDER_CODE = '20' THEN 'MALE' 
      WHEN GENDER_CODE = '30' THEN 'FEMALE' ELSE '' END
)
,sa AS (
SELECT
he.AGE
,he.GENDER_CODE
,he.PF_CODE
,COUNT(*) AS cnt
FROM SCHOOL_HEALTH he
WHERE EXITST
(SELECT 1 FROM ha WHERE he.AVERAGE_VALUE <= ha.AVERAGE_VALUE
AND he.AGE = ha.AGE
AND he.GENDER_CODE = ha.GENDER_CODE
)
GROUP BY 
he.AGE
,he.GENDER_CODE
,he.PF_CODE
)
SELECT * FROM sa
;

--AGEの降順
--GENDER_CODEの降順
;