WITH A AS (
SELECT
		SH1.AGE
		,SH1.GENDER_CODE
		,SH1.CATEGORY_CODE
		,ROU_AVG
		,ROUND(CAST(COUNT(*) AS REAL) / CAST(DISTRICT_CNT AS REAL) * 100, 1) || '%' AS PER
	FROM
		SCHOOL_HEALTH SH1
	INNER JOIN(
		SELECT
			AGE
			,GENDER_CODE
			,CATEGORY_CODE
			,ROUND(AVG(AVERAGE_VALUE),1) AS ROU_AVG
			,COUNT(*) AS DISTRICT_CNT
		FROM
			SCHOOL_HEALTH
		WHERE
			SURVEY_YEAR = '2019'
		GROUP BY
			AGE
			,GENDER_CODE
			,CATEGORY_CODE) SH2
		ON SH1.AGE = SH2.AGE AND SH1.GENDER_CODE = SH2.GENDER_CODE AND SH1.CATEGORY_CODE = SH2.CATEGORY_CODE
	WHERE
		SH1.SURVEY_YEAR = '2019'
		AND ROU_AVG <= SH1.AVERAGE_VALUE
	GROUP BY
			SH1.AGE
			,SH1.GENDER_CODE
			,SH1.CATEGORY_CODE
)
SELECT
	H.AGE AS AGE
	,CASE H.GENDER_CODE 
		WHEN '20' THEN 'MALE'
		WHEN '30' THEN 'FEMALE'
	END AS GENDER
	,H.ROU_AVG AS H_AVG
	,H.PER AS H_PER
	,W.ROU_AVG AS W_AVG
	,W.PER AS W_PER
FROM
	(SELECT * FROM A WHERE CATEGORY_CODE ='10') H
	INNER JOIN (SELECT * FROM A WHERE CATEGORY_CODE = '20') W
	ON H.AGE = W.AGE AND H.GENDER_CODE = W.GENDER_CODE
ORDER BY
	H.AGE DESC
	,H.GENDER_CODE DESC
;