WITH WHAVG(WH_GENDER,WH_AGE,WH_AVG) AS (
    SELECT GENDER_CODE,AGE,
           ROUND(AVG(AVERAGE_VALUE),1)
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    AND CATEGORY_CODE = '10'
    GROUP BY CATEGORY_CODE,GENDER_CODE,AGE
)
,
     WWAVG(WW_GENDER,WW_AGE,WW_AVG) AS (
    SELECT GENDER_CODE,AGE,
           ROUND(AVG(AVERAGE_VALUE),1)
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    AND CATEGORY_CODE = '20'
    GROUP BY CATEGORY_CODE,GENDER_CODE,AGE
)
,
     PFHAVG(PFH_CODE,PFH_GENDER,PFH_AGE,PFH_AVG) AS (
    SELECT PF_CODE,GENDER_CODE,AGE,ROUND(AVG(AVERAGE_VALUE),1)
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    AND CATEGORY_CODE = '10'
    GROUP BY PF_CODE,GENDER_CODE,AGE
)
,
     PFWAVG(PFW_CODE,PFW_GENDER,PFW_AGE,PFW_AVG) AS (
    SELECT PF_CODE,GENDER_CODE,AGE,ROUND(AVG(AVERAGE_VALUE),1)
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    AND CATEGORY_CODE = '20'
    GROUP BY PF_CODE,GENDER_CODE,AGE
)
,
     HCNT(H_GENDER,H_AGE,H_CNT) AS (
    SELECT  PFH_GENDER,PFH_AGE,
            COUNT(PFH_AVG)
    FROM PFHAVG JOIN WHAVG ON
        (PFH_GENDER = WH_GENDER
        AND PFH_AGE = WH_AGE)
    WHERE PFH_AVG >= WH_AVG
    GROUP BY PFH_GENDER,PFH_AGE
)
,
      WCNT(W_GENDER,W_AGE,W_CNT) AS (
    SELECT  PFW_GENDER,PFW_AGE,
            COUNT(PFW_AVG)
    FROM PFWAVG JOIN WWAVG ON
        (PFW_GENDER = WW_GENDER
        AND PFW_AGE = WW_AGE)
    WHERE PFW_AVG >= WW_AVG
    GROUP BY PFW_GENDER,PFW_AGE
)
,
     COUNTPFH(HGEN,HAG,PFHCNT) AS (
    SELECT GENDER_CODE,AGE,COUNT(DISTINCT PF_CODE)
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    AND CATEGORY_CODE = '10'
    GROUP BY GENDER_CODE,AGE
)
,
     COUNTPFW(WGEN,WAG,PFWCNT) AS (
    SELECT GENDER_CODE,AGE,COUNT(DISTINCT PF_CODE)
    FROM SCHOOL_HEALTH
    WHERE SURVEY_YEAR = 2019
    AND CATEGORY_CODE = '20'
    GROUP BY GENDER_CODE,AGE
)


SELECT W_AGE AS AGE,
       (CASE W_GENDER
       WHEN '20' THEN 'MALE'
       ELSE 'FAMALE'
       END ) AS GENDER,
       WH_AVG AS H_AVG,
       ROUND(H_CNT * 1.0 / PFHCNT * 100,1) || '%' AS H_PER,
       WW_AVG AS W_AVG,
       ROUND(W_CNT * 1.0 / PFWCNT * 100,1) || '%' AS W_PER
FROM WWAVG JOIN WHAVG ON (WH_GENDER = WW_GENDER AND WH_AGE = WW_AGE)
     JOIN HCNT ON (WH_GENDER = H_GENDER AND WH_AGE = H_AGE)
     JOIN WCNT ON (WH_GENDER = W_GENDER AND WH_AGE = W_AGE)
     JOIN COUNTPFH ON (WH_GENDER = HGEN AND WH_AGE = HAG)
     JOIN COUNTPFW ON (WH_GENDER = WGEN AND WH_AGE = WAG)
ORDER BY W_AGE DESC,W_GENDER DESC