WITH AVERAGE_TABLE AS (
    SELECT ROUND((CAST(COUNT(PF_CODE) AS REAL)/CAST(AVERAGE.PF_COUNT AS REAL))*100,1) PER ,SH.AGE,SH.GENDER_CODE,SH.CATEGORY_CODE,AVERAGE.AVG FROM SCHOOL_HEALTH SH 
        INNER JOIN (
            SELECT
                AGE,
                GENDER_CODE,
                CATEGORY_CODE,
                ROUND(AVG(AVERAGE_VALUE),1) AVG,
                COUNT(PF_CODE) PF_COUNT
                FROM SCHOOL_HEALTH
                WHERE SURVEY_YEAR = 2019 AND AVERAGE_VALUE IS NOT NULL
                GROUP BY CATEGORY_CODE, GENDER_CODE, AGE
                ) AVERAGE ON SH.AGE = AVERAGE.AGE AND SH.GENDER_CODE = AVERAGE.GENDER_CODE AND SH.CATEGORY_CODE = AVERAGE.CATEGORY_CODE
        WHERE SH.AVERAGE_VALUE >= AVG
        GROUP BY SH.AGE,SH.GENDER_CODE,SH.CATEGORY_CODE
        )
SELECT H.AGE AGE,
    CASE
        WHEN H.GENDER_CODE=20 THEN 'MALE'
        WHEN H.GENDER_CODE=30 THEN 'FEMALE'
        ELSE ''
    END GENDER,
    H_AVG,
    H_PER||'%' H_PER,
    W_AVG,
    W_PER||'%' W_PER
 FROM (SELECT AGE,GENDER_CODE,AVG H_AVG,PER H_PER FROM AVERAGE_TABLE WHERE CATEGORY_CODE = 10) H
    INNER JOIN (SELECT AGE,GENDER_CODE,AVG W_AVG,PER W_PER FROM AVERAGE_TABLE 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