WITH ha(AGE, GENDER_CODE, A, C) AS (SELECT AGE, GENDER_CODE, AVG(AVERAGE_VALUE), COUNT(AVERAGE_VALUE) FROM SCHOOL_HEALTH WHERE SURVEY_YEAR = 2019 AND CATEGORY_CODE = 10 GROUP BY AGE, GENDER_CODE),
 hp(AGE, GENDER_CODE, P) AS (SELECT AGE, GENDER_CODE, COUNT(AVERAGE_VALUE) FROM SCHOOL_HEALTH JOIN ha USING (AGE, GENDER_CODE) WHERE SURVEY_YEAR = 2019 AND CATEGORY_CODE = 10 AND AVERAGE_VALUE >= ha.A GROUP BY AGE, GENDER_CODE),
 wa(AGE, GENDER_CODE, A, C) AS (SELECT AGE, GENDER_CODE, AVG(AVERAGE_VALUE), COUNT(AVERAGE_VALUE) FROM SCHOOL_HEALTH WHERE SURVEY_YEAR = 2019 AND CATEGORY_CODE = 20 GROUP BY AGE, GENDER_CODE),
 wp(AGE, GENDER_CODE, P) AS (SELECT AGE, GENDER_CODE, COUNT(AVERAGE_VALUE) FROM SCHOOL_HEALTH JOIN wa USING (AGE, GENDER_CODE) WHERE SURVEY_YEAR = 2019 AND CATEGORY_CODE = 20 AND AVERAGE_VALUE >= wa.A GROUP BY AGE, GENDER_CODE)
SELECT AGE, CASE GENDER_CODE WHEN 20 THEN 'MALE' ELSE 'FEMALE' END AS GENDER,
ROUND(ha.A, 1) AS H_AVG, printf("%.1f%%", ROUND(hp.P * 100.0 / ha.C, 1)) AS H_PER,
ROUND(wa.A, 1) AS W_AVG, printf("%.1f%%", ROUND(wp.P * 100.0 / wa.C, 1)) AS W_PER
FROM SCHOOL_HEALTH
LEFT JOIN ha USING (AGE, GENDER_CODE)
LEFT JOIN hp USING (AGE, GENDER_CODE)
LEFT JOIN wa USING (AGE, GENDER_CODE)
LEFT JOIN wp USING (AGE, GENDER_CODE)
WHERE SURVEY_YEAR = 2019 GROUP BY AGE, GENDER_CODE ORDER BY 1 DESC, GENDER_CODE DESC;