WITH tmp1 AS (
    SELECT 
        CATEGORY_CODE
        ,GENDER_CODE
        ,AGE
        ,ROUND(AVG(AVERAGE_VALUE),1) AS ALL_AVERAGE
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    GROUP BY 1,2,3
),tmp2 AS (
    SELECT
        T1.CATEGORY_CODE
        ,T1.GENDER_CODE
        ,T1.AGE
        ,PF_CODE
        ,AVG(AVERAGE_VALUE) AS PF_AVERAGE
        ,ALL_AVERAGE
        FROM SCHOOL_HEALTH T1
        INNER JOIN tmp1
        ON T1.CATEGORY_CODE = tmp1.CATEGORY_CODE
          AND T1.GENDER_CODE = tmp1.GENDER_CODE
          AND T1.AGE = tmp1.AGE
        WHERE SURVEY_YEAR = 2019
        GROUP BY 1,2,3,4
),tmp3 AS (
    SELECT
        CATEGORY_CODE
        ,GENDER_CODE
        ,AGE
        ,ALL_AVERAGE
        ,ROUND(SUM(CASE WHEN ALL_AVERAGE <= PF_AVERAGE THEN 1 ELSE 0 END) * 100.0 / COUNT(*),1) || '%' AS PER
        FROM tmp2
        GROUP BY 1,2,3
),result AS (
    SELECT
        H.AGE AS AGE
        ,CASE WHEN H.GENDER_CODE = '20' THEN 'MALE' ELSE 'FEMALE' END AS GENDER
        ,H.ALL_AVERAGE AS H_AVG
        ,H.PER AS H_PER
        ,W.ALL_AVERAGE AS W_AVG
        ,W.PER AS W_PER
        FROM tmp3  H
        INNER JOIN tmp3  W
        ON H.AGE = W.AGE AND H.GENDER_CODE = W.GENDER_CODE
        WHERE H.CATEGORY_CODE = '10' AND W.CATEGORY_CODE = '20'
        ORDER BY 1 Desc,2
)
SELECT * FROM result