with a as (select CATEGORY_CODE, GENDER_CODE, AGE, round(avg(AVERAGE_VALUE), 1) as AVGV
from SCHOOL_HEALTH
where SURVEY_YEAR = 2019
group by CATEGORY_CODE, GENDER_CODE, AGE), 
b as (select CATEGORY_CODE, GENDER_CODE, AGE, count(*) as CNT_PER from SCHOOL_HEALTH
left join a using (CATEGORY_CODE, GENDER_CODE, AGE)
where AVERAGE_VALUE >= AVGV
group by CATEGORY_CODE, GENDER_CODE, AGE),
c as (select CATEGORY_CODE, GENDER_CODE, AGE, count(*) as CNT from SCHOOL_HEALTH
left join a using (CATEGORY_CODE, GENDER_CODE, AGE)
group by CATEGORY_CODE, GENDER_CODE, AGE),
d as (
select AGE, (case GENDER_CODE when 20 then "MALE" else "FEMALE" end) as GENDER, 
AVGV AS H_AVG,
substr(cast(round(cast (CNT_PER as float)* 100 / cast (CNT as float), 1) as real) || '000', 1, length(cast(round(cast (CNT_PER as float)* 100 / cast (CNT as float), 1) as integer)) + 2) || "%" as H_PER
from a
left join b using (CATEGORY_CODE, GENDER_CODE, AGE)
left join c using (CATEGORY_CODE, GENDER_CODE, AGE)
where CATEGORY_CODE = "10"),
e as (
select AGE, (case GENDER_CODE when 20 then "MALE" else "FEMALE" end) as GENDER, 
AVGV AS W_AVG,
substr(cast(round(cast (CNT_PER as float)* 100 / cast (CNT as float), 1) as real) || '000', 1, length(cast(round(cast (CNT_PER as float)* 100 / cast (CNT as float), 1) as integer)) + 2) || "%" as W_PER
from a
left join b using (CATEGORY_CODE, GENDER_CODE, AGE)
left join c using (CATEGORY_CODE, GENDER_CODE, AGE)
where CATEGORY_CODE = "20")
select AGE, GENDER, H_AVG, H_PER,W_AVG, W_PER from d left join e using(GENDER, AGE)
order by AGE desc, GENDER asc