WITH H_AVG AS (
SELECT AGE,GENDER_CODE,ROUND(AVG(AVERAGE_VALUE),1) AS H_AVG,COUNT(*) AS CNT
FROM SCHOOL_HEALTH
WHERE SURVEY_YEAR = 2019
AND CATEGORY_CODE = '10'
GROUP BY AGE,GENDER_CODE
),
H_PER AS (
SELECT A.AGE,A.GENDER_CODE,COUNT(*) AS CNT
FROM SCHOOL_HEALTH A
INNER JOIN H_AVG B
ON A.AGE = B.AGE
AND A.GENDER_CODE = B.GENDER_CODE
AND A.AVERAGE_VALUE >= B.H_AVG
WHERE A.SURVEY_YEAR = 2019
AND A.CATEGORY_CODE = '10'
GROUP BY A.AGE,A.GENDER_CODE
),
W_AVG AS (
SELECT AGE,GENDER_CODE,ROUND(AVG(AVERAGE_VALUE),1) AS W_AVG,COUNT(*) AS CNT
FROM SCHOOL_HEALTH
WHERE SURVEY_YEAR = 2019
AND CATEGORY_CODE = '20'
GROUP BY AGE,GENDER_CODE
),
W_PER AS (
SELECT A.AGE,A.GENDER_CODE,COUNT(*) AS CNT
FROM SCHOOL_HEALTH A
INNER JOIN W_AVG B
ON A.AGE = B.AGE
AND A.GENDER_CODE = B.GENDER_CODE
AND A.AVERAGE_VALUE >= B.W_AVG
WHERE A.SURVEY_YEAR = 2019
AND A.CATEGORY_CODE = '20'
GROUP BY A.AGE,A.GENDER_CODE
)
SELECT HA.AGE AS AGE,
CASE WHEN HA.GENDER_CODE = '20' THEN 'MALE'
WHEN HA.GENDER_CODE = '30' THEN 'FEMALE'
ELSE '' END AS GENDER,
HA.H_AVG AS H_AVG,
ROUND(CAST(HP.CNT AS REAL) / CAST(HA.CNT AS REAL) * 100,1) || '%' AS H_PER,
WA.W_AVG AS W_AVG,
ROUND(CAST(WP.CNT AS REAL) / CAST(WA.CNT AS REAL) * 100,1) || '%' AS W_PER
FROM H_AVG HA
INNER JOIN H_PER HP
ON HA.AGE = HP.AGE 
AND HA.GENDER_CODE = HP.GENDER_CODE
INNER JOIN W_AVG WA
ON HA.AGE = WA.AGE 
AND HA.GENDER_CODE = WA.GENDER_CODE
INNER JOIN W_PER WP
ON HA.AGE = WP.AGE 
AND HA.GENDER_CODE = WP.GENDER_CODE
ORDER BY HA.AGE DESC,HA.GENDER_CODE DESC;