with tmp as (
select
--全地区の年齢性別別の身長と体重の合計値とレコード数の算出
    age
    ,gender_code
    ,category_code
    ,sum(average_value) sum_value
    ,count(*) cnt
from
    school_health
where
    survey_year = 2019
group by
    age, gender_code, category_code
), tmp2 as (
--全地区の年齢性別別の身長と体重の平均値の算出
select
    age AGE
    ,gender_code
    ,case
        when gender_code = '20' then 'MALE'
        else 'FEMALE'
     end as GENDER
    ,category_code
    ,case
        when category_code = '10' then 'HEIGHT'
        else 'WEIGHT'
     end as CATEGORY
    ,sum_value
    ,cnt
    ,round(sum_value * 1.0 / cnt, 1) AVG_VALUE
from
    tmp
), tmp3 as (
--平均値以上かそれ未満かの判定
select
    t.age
    ,t.gender
    ,t.category
    ,t.sum_value
    ,t.cnt
    ,t.avg_value
    ,sh.average_value
    ,case when average_value >= avg_value then 1 else 0 end as avg_over_flg
from
    tmp2 t left join school_health sh
    on t.age = sh.age
    and t.gender_code = sh.gender_code
    and t.category_code = sh.category_code
where
    survey_year = 2019
), tmp4 as (
select
--平均値以上の地区の割合の算出
    age
    ,gender
    ,category
    ,cnt
    ,avg_value
    ,sum(avg_over_flg) cnt_over_avg
    ,sum(avg_over_flg)*1.0 / cnt per
from
    tmp3
group by
    age
    ,gender
    ,category
    ,cnt
    ,avg_value
)
select
--縦持ちを横持へ変換
    age AGE
    ,gender GENDER
    ,max(case
        when category = 'HEIGHT' then AVG_VALUE
        else null
     end) as H_AVG
    ,round(max(case
        when category = 'HEIGHT' then per
        else null
     end) * 100, 1) || '%' as H_PER
    ,max(case
        when category = 'WEIGHT' then AVG_VALUE
        else null
     end) as W_AVG
    ,round(max(case
        when category = 'WEIGHT' then per
        else null
     end) * 100, 1) || '%' as W_PER
from
    tmp4
group by
    age, gender
order by
    age desc, gender
;