WITH SUB1 AS (
	SELECT
		AGE
		, PF_CODE
		, GENDER_CODE
		, CATEGORY_CODE
		, AVERAGE_VALUE
		, AVG(AVERAGE_VALUE) OVER (PARTITION BY AGE, GENDER_CODE, CATEGORY_CODE) DAVG
		, AVERAGE_VALUE - AVG(AVERAGE_VALUE) OVER (PARTITION BY AGE, GENDER_CODE, CATEGORY_CODE) DIFF
	FROM
		SCHOOL_HEALTH
	WHERE
		SURVEY_YEAR = 2019
)
, SUB2 AS (
    SELECT
    	AGE
    	, GENDER_CODE
    	, CATEGORY_CODE
    	, AVG(AVERAGE_VALUE) AVG_V
    	, MAX(DAVG)
    	, SUM(CASE WHEN DIFF >= 0 THEN 1 ELSE 0 END) AVG_U
    	, SUM(CASE WHEN DIFF < 0  THEN 1 ELSE 0 END) AVG_D
    FROM
    	SUB1
    GROUP BY
    	AGE
    	, GENDER_CODE
    	, CATEGORY_CODE
)
SELECT
    AGE
    , CASE WHEN GENDER_CODE = '20' THEN 'MALE' ELSE 'FEMALE' END AS GENDER
    , MAX(CASE WHEN CATEGORY_CODE = '10' THEN ROUND(AVG_V, 1) ELSE 0 END) AS 'H_AVG'
    , MAX(CASE WHEN CATEGORY_CODE = '10' THEN ROUND(CAST(AVG_U AS REAL) / (AVG_U + AVG_D) * 100, 1) ELSE 0 END)  || '%' AS 'H_PER'
    , MAX(CASE WHEN CATEGORY_CODE = '20' THEN ROUND(AVG_V, 1) ELSE 0 END) AS 'W_AVG'
    , MAX(CASE WHEN CATEGORY_CODE = '20' THEN ROUND(CAST(AVG_U AS REAL) / (AVG_U + AVG_D) * 100, 1) ELSE 0 END)  || '%' AS 'W_PER'
FROM
    SUB2
GROUP BY
    AGE
    , GENDER_CODE
ORDER BY
    AGE DESC
    , GENDER_CODE DESC
;