WITH
  AverageValues AS (
    SELECT
      AGE
    , GENDER_CODE
    , CATEGORY_CODE
    , ROUND(AVG(AVERAGE_VALUE), 1) AS OverallAverage
    FROM
      SCHOOL_HEALTH
    WHERE
      SURVEY_YEAR = 2019
    GROUP BY
      AGE
    , GENDER_CODE
    , CATEGORY_CODE
  )
, AboveAverageCounts AS (
    SELECT
      SH.AGE
    , SH.GENDER_CODE
    , SH.CATEGORY_CODE
    , COUNT(*) AS CountAboveAvg
    FROM
      SCHOOL_HEALTH SH
      JOIN AverageValues AV ON SH.AGE = AV.AGE
      AND SH.GENDER_CODE = AV.GENDER_CODE
      AND SH.CATEGORY_CODE = AV.CATEGORY_CODE
      AND SH.AVERAGE_VALUE >= AV.OverallAverage
    WHERE
      SH.SURVEY_YEAR = 2019
    GROUP BY
      SH.AGE
    , SH.GENDER_CODE
    , SH.CATEGORY_CODE
  )
SELECT
  SH.AGE
, CASE
    WHEN SH.GENDER_CODE = '20' THEN 'MALE'
    ELSE 'FEMALE'
  END AS GENDER
, ROUND(
    AVG(
      CASE
        WHEN SH.CATEGORY_CODE = '10' THEN SH.AVERAGE_VALUE
      END
    )
  , 1
  ) AS H_AVG
, ROUND(
    100.0 * COALESCE(
      MAX(
        CASE
          WHEN SH.CATEGORY_CODE = '10' THEN AAC.CountAboveAvg
        END
      )
    , 0
    ) / COUNT(
      CASE
        WHEN SH.CATEGORY_CODE = '10' THEN 1
      END
    )
  , 1
  ) || '％' AS H_PER
, ROUND(
    AVG(
      CASE
        WHEN SH.CATEGORY_CODE = '20' THEN SH.AVERAGE_VALUE
      END
    )
  , 1
  ) AS W_AVG
, ROUND(
    100.0 * COALESCE(
      MAX(
        CASE
          WHEN SH.CATEGORY_CODE = '20' THEN AAC.CountAboveAvg
        END
      )
    , 0
    ) / COUNT(
      CASE
        WHEN SH.CATEGORY_CODE = '20' THEN 1
      END
    )
  , 1
  ) || '％' AS W_PER
FROM
  SCHOOL_HEALTH SH
  LEFT JOIN AboveAverageCounts AAC ON SH.AGE = AAC.AGE
  AND SH.GENDER_CODE = AAC.GENDER_CODE
  AND SH.CATEGORY_CODE = AAC.CATEGORY_CODE
WHERE
  SH.SURVEY_YEAR = 2019
GROUP BY
  SH.AGE
, SH.GENDER_CODE
ORDER BY
  SH.AGE DESC
, SH.GENDER_CODE DESC;