-- 平均値テーブルを作成
WITH RECURSIVE TBL_AVG(SURVEY_YEAR, AGE, GENDER_CODE, H_AVG, W_AVG) AS (
SELECT
    SURVEY_YEAR
  , AGE
  , GENDER_CODE
  , 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
GROUP BY
    SURVEY_YEAR
  , AGE
  , GENDER_CODE
)

-- ここから実際のSELECT文
SELECT
    TB_SH1.AGE AS AGE
  , CASE
        WHEN TB_SH1.GENDER_CODE = '20' THEN 'MALE'
        WHEN TB_SH1.GENDER_CODE = '30' THEN 'FEMALE'
    END AS GENDER
  , TBL_AVG.H_AVG AS H_AVG
  , ROUND(SUM(CATEGORY_CODE = '10' AND AVERAGE_VALUE >= TBL_AVG.H_AVG) * 100.0 / SUM(CATEGORY_CODE = '10'), 1) || '%' AS H_PER
  , TBL_AVG.W_AVG AS W_AVG
  , ROUND(SUM(CATEGORY_CODE = '20' AND AVERAGE_VALUE >= TBL_AVG.W_AVG) * 100.0 / SUM(CATEGORY_CODE = '20'), 1) || '%' AS W_PER
FROM
    SCHOOL_HEALTH AS TB_SH1
    LEFT JOIN(
        SELECT *
        FROM TBL_AVG
    ) TBL_AVG ON
           TB_SH1.SURVEY_YEAR = TBL_AVG.SURVEY_YEAR
       AND TB_SH1.GENDER_CODE = TBL_AVG.GENDER_CODE
       AND TB_SH1.AGE         = TBL_AVG.AGE
WHERE
   TB_SH1.SURVEY_YEAR = '2019'
GROUP BY
    TB_SH1.SURVEY_YEAR
  , TB_SH1.AGE
  , TB_SH1.GENDER_CODE
ORDER BY
    TB_SH1.AGE DESC,
    TB_SH1.GENDER_CODE DESC
;