WITH AVGtable AS ( --身長、体重平均
	SELECT
		AGE
		,GENDER_CODE
		,CATEGORY_CODE
		,ROUND(AVG(AVERAGE_VALUE),1) AS H_AVG
	FROM SCHOOL_HEALTH
	WHERE SURVEY_YEAR = 2019
	GROUP BY 1,2,3
)
,H_join AS (--身長情報結び付け
	SELECT
		t0.PF_CODE
		,t0.AGE
		,t0.GENDER_CODE
		,t0.CATEGORY_CODE
		,t1.H_AVG
		,t0.AVERAGE_VALUE
	FROM SCHOOL_HEALTH AS t0
	INNER JOIN AVGtable AS t1
		ON t0.AGE = t1.AGE
			AND t0.GENDER_CODE = t1.GENDER_CODE
			AND t0.CATEGORY_CODE = t1.CATEGORY_CODE
	WHERE t0.SURVEY_YEAR = 2019
			AND t0.CATEGORY_CODE = '10'
)
,H_avg AS ( --身長
	SELECT
		AGE
		,GENDER_CODE
		,CATEGORY_CODE
		,SUM(CASE WHEN AVERAGE_VALUE >= H_AVG THEN 1 ELSE 0 END) AS H_overAvg
		,COUNT(PF_CODE) AS Cnt

	FROM H_join
	GROUP BY 1,2,3
)
,W_join AS (--体重情報結び付け
	SELECT
		t0.PF_CODE
		,t0.AGE
		,t0.GENDER_CODE
		,t0.CATEGORY_CODE
		,t1.H_AVG AS W_AVG
		,t0.AVERAGE_VALUE
	FROM SCHOOL_HEALTH AS t0
	INNER JOIN AVGtable AS t1
		ON t0.AGE = t1.AGE
			AND t0.GENDER_CODE = t1.GENDER_CODE
			AND t0.CATEGORY_CODE = t1.CATEGORY_CODE
	WHERE t0.SURVEY_YEAR = 2019
			AND t0.CATEGORY_CODE = '20'
)
,W_avg AS ( --体重
	SELECT
		AGE
		,GENDER_CODE
		,CATEGORY_CODE
		,SUM(CASE WHEN AVERAGE_VALUE >= W_AVG THEN 1 ELSE 0 END) AS W_overAvg
		,COUNT(PF_CODE) AS Cnt

	FROM W_join
	GROUP BY 1,2,3
)
,H_table AS (
	SELECT
		th.AGE
		,th.GENDER_CODE
		,th.CATEGORY_CODE
		,t1.H_AVG
		,CAST(ROUND(CAST(th.H_overAvg AS REAL)/th.Cnt*100,1) AS TEXT) || '%' AS H_PER
	FROM H_avg AS th
	INNER JOIN AVGtable AS t1
		ON th.AGE = t1.AGE
			AND th.GENDER_CODE = t1.GENDER_CODE
			AND th.CATEGORY_CODE = t1.CATEGORY_CODE
)
,W_table AS (
	SELECT
		th.AGE
		,th.GENDER_CODE
		,th.CATEGORY_CODE
		,t1.H_AVG
		,CAST(ROUND(CAST(th.W_overAvg AS REAL)/th.Cnt*100,1) AS TEXT) || '%' AS W_PER
	FROM W_avg AS th
	INNER JOIN AVGtable AS t1
		ON th.AGE = t1.AGE
			AND th.GENDER_CODE = t1.GENDER_CODE
			AND th.CATEGORY_CODE = t1.CATEGORY_CODE
)
SELECT
	t0.AGE
	,CASE t0.GENDER_CODE
		WHEN '20'THEN 'MALE'
		WHEN '30'THEN 'FEMALE' END AS GENDER
	,t0.H_AVG
	,t0.H_PER
	,t1.H_AVG AS W_AVG
	,t1.W_PER
FROM H_table AS t0
INNER JOIN W_table AS t1
	ON t0.AGE = t1.AGE
		AND t0.GENDER_CODE = t1.GENDER_CODE
ORDER BY t0.AGE DESC,t0.GENDER_CODE DESC