with a as (select CATEGORY_CODE, GENDER_CODE, AGE, round(avg(AVERAGE_VALUE)+0.0001, 1)  as AVGV, avg(AVERAGE_VALUE) as AVGVT
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
inner join a using (CATEGORY_CODE, GENDER_CODE, AGE)
where round(AVERAGE_VALUE+0.0001, 1) >= AVGV and SURVEY_YEAR = 2019
group by CATEGORY_CODE, GENDER_CODE, AGE),
c as (select CATEGORY_CODE, GENDER_CODE, AGE, count(*) as CNT from SCHOOL_HEALTH
inner join a using (CATEGORY_CODE, GENDER_CODE, AGE)
where SURVEY_YEAR = 2019
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