WITH H_AVG AS(
    SELECT 
        PF_CODE AS PC,
        CATEGORY_CODE AS CC,
        GENDER_CODE AS GC,
        AVERAGE_VALUE AS VAL
    FROM
        SCHOOL_HEALTH
    WHERE
    SURVEY_YEAR = 2019 AND
    CC = '10'
),

W_AVG AS(
    SELECT 
        PF_CODE AS PC,
        CATEGORY_CODE AS CC,
        GENDER_CODE AS GC,
        AVERAGE_VALUE AS VAL
    FROM
        SCHOOL_HEALTH
    WHERE
    SURVEY_YEAR = 2019 AND
    CC = '20'
),
ALL_AVG AS(
SELECT 
    AGE,
    CASE 
       WHEN GENDER_CODE = 20 THEN 'MALE'
       WHEN  GENDER_CODE = 30 THEN 'FEMALE'
    END AS GENDER,
    ROUND(AVG(H.VAL),2) AS H_AVG,
    ROUND(AVG(W.VAL),2) AS W_PER
FROM
    SCHOOL_HEALTH AS S
    LEFT OUTER JOIN H_AVG AS H 
        ON 
            S.PF_CODE = H.PC AND
            S.CATEGORY_CODE = H.CC AND
            S.GENDER_CODE = H.GC
    LEFT OUTER JOIN W_AVG AS W
        ON 
            S.PF_CODE = W.PC AND
            S.CATEGORY_CODE = W.CC AND
            S.GENDER_CODE = W.GC
WHERE
    SURVEY_YEAR = 2019
GROUP BY
    AGE, GENDER
ORDER BY
    AGE DESC,
    GENDER_CODE DESC
)