WITH HAVG AS
(SELECT
     AGE,
     GENDER_CODE,
     ROUND(AVG(AVERAGE_VALUE), 1) AS H_AVG
 FROM
     SCHOOL_HEALTH
 WHERE
     CATEGORY_CODE = '10'
 AND SURVEY_YEAR   = 2019
 GROUP BY
     AGE,
     GENDER_CODE
),
 WAVG AS
(SELECT
     AGE,
     GENDER_CODE,
     ROUND(AVG(AVERAGE_VALUE), 1) AS W_AVG
 FROM
     SCHOOL_HEALTH
 WHERE
     CATEGORY_CODE = '20'
 AND SURVEY_YEAR   = 2019
 GROUP BY
     AGE,
     GENDER_CODE
),
 H_TARGET AS
(SELECT
     PF_CODE,
     AGE,
     GENDER_CODE,
     AVERAGE_VALUE
 FROM
     SCHOOL_HEALTH
 WHERE
     CATEGORY_CODE = '10'
 AND SURVEY_YEAR   = 2019
),
 W_TARGET AS
(SELECT
     PF_CODE,
     AGE,
     GENDER_CODE,
     AVERAGE_VALUE
 FROM
     SCHOOL_HEALTH
 WHERE
     CATEGORY_CODE = '20'
 AND SURVEY_YEAR   = 2019
)
SELECT
	HAVG.AGE AS AGE,
	CASE HAVG.GENDER_CODE 
	     WHEN '20' THEN 'MALE'
	     WHEN '30' THEN 'FEMALE'
	     ELSE ''
	END AS GENDER,
	HAVG.H_AVG AS H_AVG,
	HT.PER || '%' AS H_PER,
	WAVG.W_AVG AS W_AVG,
	WT.PER || '%' AS W_PER
FROM
    HAVG 
    LEFT JOIN WAVG
    ON  HAVG.AGE = WAVG.AGE
    AND HAVG.GENDER_CODE = WAVG.GENDER_CODE
    LEFT JOIN 
    ( SELECT 
          SH.AGE,
          SH.GENDER_CODE,
          ROUND(CAST(COUNT(PF_CODE) AS REAL) / CAST(TOTA.TOT AS REAL) * 100, 1) AS PER
      FROM
          H_TARGET SH
          INNER JOIN HAVG
          ON  SH.AGE = HAVG.AGE
          AND SH.GENDER_CODE = HAVG.GENDER_CODE
          INNER JOIN 
          (SELECT AGE, GENDER_CODE, COUNT(PF_CODE) AS TOT FROM H_TARGET GROUP BY AGE, GENDER_CODE) TOTA
          ON  SH.AGE = TOTA.AGE
          AND SH.GENDER_CODE = TOTA.GENDER_CODE
      WHERE
          SH.AVERAGE_VALUE >= HAVG.H_AVG
      GROUP BY
          SH.AGE,
          SH.GENDER_CODE
    ) HT
    ON  HAVG.AGE = HT.AGE
    AND HAVG.GENDER_CODE = HT.GENDER_CODE
    LEFT JOIN 
    ( SELECT 
          SH.AGE,
          SH.GENDER_CODE,
          ROUND(CAST(COUNT(PF_CODE) AS REAL) / CAST(TOTA.TOT AS REAL) * 100, 1) AS PER
      FROM
          W_TARGET SH
          INNER JOIN WAVG
          ON  SH.AGE = WAVG.AGE
          AND SH.GENDER_CODE = WAVG.GENDER_CODE
          INNER JOIN 
          (SELECT AGE, GENDER_CODE, COUNT(PF_CODE) AS TOT FROM W_TARGET GROUP BY AGE, GENDER_CODE) TOTA
          ON  SH.AGE = TOTA.AGE
          AND SH.GENDER_CODE = TOTA.GENDER_CODE
      WHERE
          SH.AVERAGE_VALUE >= WAVG.W_AVG
      GROUP BY
          SH.AGE,
          SH.GENDER_CODE
    ) WT
    ON  HAVG.AGE = WT.AGE
    AND HAVG.GENDER_CODE = WT.GENDER_CODE
ORDER BY
	HAVG.AGE DESC,
	HAVG.GENDER_CODE DESC