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,
        COUNT(DISTINCT CASE WHEN CATEGORY_CODE = '10' AND ROUND(AVERAGE_VALUE, 1) >= H_AVG THEN PF_CODE END) * 1.0 / COUNT(DISTINCT CASE WHEN CATEGORY_CODE = '10' THEN PF_CODE END) AS H_PER,
        COUNT(DISTINCT CASE WHEN CATEGORY_CODE = '20' AND ROUND(AVERAGE_VALUE, 1) >= W_AVG THEN PF_CODE END) * 1.0 / COUNT(DISTINCT CASE WHEN CATEGORY_CODE = '20' THEN PF_CODE END) AS W_PER
    FROM
        SCHOOL_HEALTH
    INNER JOIN
        AvgValues
        USING (AGE, GENDER_CODE)
    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,
    ROUND(p.H_PER * 100, 1) || '％' AS H_PER,
    av.W_AVG AS W_AVG,
    ROUND(p.W_PER * 100, 1) || '％' AS W_PER
FROM
    AvgValues av
INNER JOIN
    Percentage p USING (AGE, GENDER_CODE)
ORDER BY
    AGE DESC, GENDER_CODE DESC;