WITH SH_AVG AS (
    SELECT
        AGE
        ,GENDER_CODE
        ,AVG(CASE WHEN CATEGORY_CODE == '10' THEN AVERAGE_VALUE ELSE NULL END) AS H_AVG
        ,AVG(CASE WHEN CATEGORY_CODE == '20' THEN AVERAGE_VALUE ELSE NULL END) AS W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE, GENDER_CODE
    ORDER BY
        AGE DESC
        ,GENDER_CODE DESC
)
SELECT * FROM SH_AVG;WITH SH_AVG AS (
    SELECT
        AGE
        ,GENDER_CODE
        ,2019 AS SURVEY_YEAR
        ,ROUND(AVG(CASE WHEN CATEGORY_CODE == '10' THEN AVERAGE_VALUE ELSE NULL END), 1) AS H_AVG
        ,ROUND(AVG(CASE WHEN CATEGORY_CODE == '20' THEN AVERAGE_VALUE ELSE NULL END), 1) AS W_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE, GENDER_CODE
    ORDER BY
        AGE DESC
        ,GENDER_CODE DESC
)
SELECT
    sh.AGE
    ,CASE sh.GENDER_CODE 
        WHEN 20 THEN 'MALE' 
        ELSE 'FEMALE' 
        END AS GENDER
    ,ROUND(AVG(CASE WHEN sh.CATEGORY_CODE == '10' THEN sh.AVERAGE_VALUE ELSE NULL END), 1) AS H_AVG
    ,ROUND(COUNT(CASE WHEN sh.CATEGORY_CODE == '10' AND sha.H_AVG <= AVERAGE_VALUE THEN 1 ELSE NULL END) /
     CAST(COUNT(CASE WHEN sh.CATEGORY_CODE == '10' THEN 1 ELSE NULL END) AS FLOAT) * 100, 1) || '%' AS H_PER
    ,ROUND(AVG(CASE WHEN sh.CATEGORY_CODE == '20' THEN sh.AVERAGE_VALUE ELSE NULL END), 1) AS W_AVG
    ,ROUND(COUNT(CASE WHEN sh.CATEGORY_CODE == '20' AND sha.W_AVG <= AVERAGE_VALUE THEN 1 ELSE NULL END) /
     CAST(COUNT(CASE WHEN sh.CATEGORY_CODE == '20' THEN 1 ELSE NULL END) AS FLOAT) * 100, 1) || '%' AS W_PER
FROM SCHOOL_HEALTH sh
INNER JOIN SH_AVG sha
    ON sh.AGE = sha.AGE
        AND sh.GENDER_CODE = sha.GENDER_CODE
        AND sh.SURVEY_YEAR = sha.SURVEY_YEAR
GROUP BY
    sh.AGE, sh.GENDER_CODE
ORDER BY
    sh.AGE DESC
    ,sh.GENDER_CODE DESC;