WITH AvgValues AS (
    SELECT
        AGE,
        GENDER_CODE,
        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
),

Percentage AS (
    SELECT
        AGE,
        GENDER_CODE,
        ROUND(100 * COUNT(DISTINCT CASE WHEN CATEGORY_CODE = '10' AND AVERAGE_VALUE >= H_AVG THEN PF_CODE END) / COUNT(DISTINCT PF_CODE), 1) AS H_PER,
        ROUND(100 * COUNT(DISTINCT CASE WHEN CATEGORY_CODE = '20' AND AVERAGE_VALUE >= W_AVG THEN PF_CODE END) / COUNT(DISTINCT PF_CODE), 1) AS W_PER
    FROM
        SCHOOL_HEALTH
    INNER JOIN
        AvgValues
        USING (AGE, GENDER_CODE)
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        AGE, GENDER_CODE
)

SELECT
    av.AGE AS AGE,
    CASE av.GENDER_CODE WHEN '20' THEN 'MALE' WHEN '30' THEN 'FEMALE' END AS GENDER,
    av.H_AVG AS H_AVG,
    p.H_PER || '％' AS H_PER,
    av.W_AVG AS W_AVG,
    p.W_PER || '％' AS W_PER
FROM
    AvgValues av
INNER JOIN
    Percentage p USING (AGE, GENDER_CODE)
ORDER BY
    AGE DESC, GENDER_CODE DESC;