WITH SUB1 AS (
    SELECT
		CATEGORY_CODE
		, PF_CODE
		, AGE
		, GENDER_CODE
		, AVERAGE_VALUE
		, ROUND(AVG(AVERAGE_VALUE) OVER (PARTITION BY CATEGORY_CODE, AGE, GENDER_CODE), 1) AS DAVG
		, AVERAGE_VALUE - ROUND(AVG(AVERAGE_VALUE) OVER (PARTITION BY AGE, GENDER_CODE, CATEGORY_CODE), 1) AS DIFF
    FROM
        SCHOOL_HEALTH
	WHERE
		SURVEY_YEAR = 2019
)
, SUB2 AS (
    SELECT
    	AGE
    	, GENDER_CODE
    	, CATEGORY_CODE
    	, MAX(DAVG) AVG_V
    	, 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
;