WITH AVG_TBL(AGE, GENDER_CODE, GENDER, H_AVG, W_AVG) AS (
    SELECT
        AGE,
        GENDER_CODE,
        CASE
            WHEN GENDER_CODE = '20' THEN 'MALE'
            WHEN GENDER_CODE = '30' THEN 'FEMALE'
        END AS GENDER,
        ROUND(AVG(CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE END), 1) AS H_AVG,
        ROUND(AVG(CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE END), 1) AS W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE, GENDER_CODE
),
CNT_TBL(AGE, GENDER_CODE, H_CNT, W_CNT) AS (
    SELECT
        S.AGE,
        S.GENDER_CODE,
        CAST(SUM(CASE WHEN S.CATEGORY_CODE = '10' AND S.AVERAGE_VALUE >= A.H_AVG THEN 1 ELSE 0 END) AS REAL) AS H_CNT,
        CAST(SUM(CASE WHEN S.CATEGORY_CODE = '20' AND S.AVERAGE_VALUE >= A.W_AVG THEN 1 ELSE 0 END) AS REAL) AS W_CNT
    FROM 
        SCHOOL_HEALTH AS S
    LEFT JOIN 
        AVG_TBL AS A ON S.AGE = A.AGE AND S.GENDER_CODE = A.GENDER_CODE
    WHERE
        S.SURVEY_YEAR = 2019
    GROUP BY
        S.AGE, S.GENDER_CODE
)
SELECT
    A.AGE AS AGE,
    A.GENDER AS GENDER,
    A.H_AVG AS H_AVG,
    ROUND((S.H_CNT / P.PF_H_CNT) * 100, 1) || '%' AS H_PER,
    A.W_AVG AS W_AVG,
    ROUND((S.W_CNT / P.PF_W_CNT) * 100, 1) || '%' AS W_PER
FROM
    AVG_TBL AS A
LEFT JOIN 
    CNT_TBL AS S ON A.AGE = S.AGE AND A.GENDER_CODE = S.GENDER_CODE
LEFT JOIN
    (
        SELECT
            AGE,
            GENDER_CODE,
            SUM(CASE WHEN CATEGORY_CODE = '10' THEN 1 ELSE 0 END) AS PF_H_CNT,
            SUM(CASE WHEN CATEGORY_CODE = '20' THEN 1 ELSE 0 END) AS PF_W_CNT
        FROM 
            SCHOOL_HEALTH
        WHERE
            SURVEY_YEAR = 2019
        GROUP BY
            AGE, GENDER_CODE
    ) AS P ON A.AGE = P.AGE AND A.GENDER_CODE = P.GENDER_CODE
GROUP BY
    A.AGE, A.GENDER, A.H_AVG, A.W_AVG, S.H_CNT, S.W_CNT
ORDER BY
    A.AGE DESC,
    A.GENDER_CODE DESC;