WITH S1 AS (
	SELECT
		AGE
	,    GENDER_CODE
	,    ROUND(SUM(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE ELSE 0 END) / SUM(CASE WHEN CATEGORY_CODE = '10' THEN 1 ELSE 0 END), 1) AS H_AVG
	,    ROUND(SUM(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE ELSE 0 END) / SUM(CASE WHEN CATEGORY_CODE = '20' THEN 1 ELSE 0 END), 1) AS W_AVG
	FROM
		SCHOOL_HEALTH
	WHERE
		SURVEY_YEAR = 2019
	GROUP BY
		AGE
	,	GENDER_CODE
),
S2 AS (
	SELECT
		AGE
	,	GENDER_CODE
	,	PF_CODE
	,	SUM(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE ELSE 0 END) AS H2
	,	SUM(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE ELSE 0 END) AS W2
	FROM
		SCHOOL_HEALTH
	WHERE
		SURVEY_YEAR = 2019
	GROUP BY
		AGE
	,	GENDER_CODE
	,	PF_CODE
)
SELECT
	S2.AGE AS AGE
,	CASE WHEN S2.GENDER_CODE = '20' THEN 'MALE' ELSE 'FEMALE' END AS GENDER
,	ROUND(S1.H_AVG, 1) AS H_AVG
,	CAST(ROUND(CAST(SUM(CASE WHEN S2.H2 >= S1.H_AVG AND S2.H2 > 0 THEN 1 ELSE 0 END) AS REAL) 
		/ CAST(SUM(CASE WHEN S2.H2 > 0 THEN 1 ELSE 0 END) AS REAL) * 100, 1) AS TEXT) || "%" AS H_PER
,	ROUND(S1.W_AVG, 1) AS W_AVG
,	CAST(ROUND(CAST(SUM(CASE WHEN S2.W2 >= S1.W_AVG AND S2.W2 > 0 THEN 1 ELSE 0 END) AS REAL) 
		/ CAST(SUM(CASE WHEN S2.W2 > 0 THEN 1 ELSE 0 END) AS REAL) * 100, 1) AS TEXT)  || "%" AS W_PER
FROM
	S2
		INNER JOIN S1
			ON	S2.AGE = S1.AGE
			AND	S2.GENDER_CODE = S1.GENDER_CODE
GROUP BY 
	S2.AGE
,	CASE WHEN S2.GENDER_CODE = '20' THEN 'MALE' ELSE 'FEMALE' END
ORDER BY
	AGE DESC
,	S1.GENDER_CODE DESC