WITH SH_AVG AS 
(
	SELECT
		SH.AGE
		,SH.GENDER_CODE AS GENDER
		,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
		,SUM(CASE WHEN CATEGORY_CODE = 10 THEN 1 ELSE 0 END) AS H_S
		,SUM(CASE WHEN CATEGORY_CODE = 20 THEN 1 ELSE 0 END) AS W_S
	FROM SCHOOL_HEALTH SH
	WHERE SURVEY_YEAR = 2019
	GROUP BY AGE,GENDER
),
SH_PER AS
(
    SELECT
    	SA.AGE
	    ,SA.GENDER
    	,ROUND(100.0 * SUM(CASE WHEN CATEGORY_CODE = 10 AND AVERAGE_VALUE >= SA.H_AVG THEN 1 ELSE 0 END) / H_S,1) || '%' AS H_PER
	    ,ROUND(100.0 * SUM(CASE WHEN CATEGORY_CODE = 20 AND AVERAGE_VALUE >= SA.W_AVG THEN 1 ELSE 0 END) / W_S,1) || '%' AS W_PER
    FROM SCHOOL_HEALTH SH
	    ,SH_AVG SA
    WHERE SURVEY_YEAR = 2019
    AND SH.AGE = SA.AGE
    AND SH.GENDER_CODE = SA.GENDER
    GROUP BY
        SA.AGE
	    ,SA.GENDER
)
SELECT
	SA.AGE
    ,CASE WHEN SA.GENDER = 20 THEN 'MALE' ELSE 'FEMALE' END AS GENDER
    ,SA.H_AVG
    ,SP.H_PER
    ,SA.W_AVG
    ,SP.W_PER
FROM SH_AVG SA
    ,SH_PER SP
WHERE SP.AGE = SA.AGE
AND SP.GENDER = SA.GENDER
ORDER BY SA.AGE DESC
        ,SA.GENDER DESC
 ;