with averages as (
    select
        age,
        gender_code,
        -- 身長
        round(
            sum(case when category_code = '10' then average_value else 0 end) /
                sum(case when category_code = '10' then 1 else 0 end),
            1
        ) as avg_height,
        round(
            sum(case when category_code = '20' then average_value else 0 end) /
                sum(case when category_code = '20' then 1 else 0 end),
            1
        ) as avg_weight
    from
        school_health
    where
        survey_year = '2019'
    group by
        age, gender_code
),
averages_district as (
    select
        age,
        gender_code,
        pf_code,
        sum(case when category_code = '10' then average_value else 0 end) as height,
        sum(case when category_code = '20' then average_value else 0 end) as weight
    from
        school_health
    where
        survey_year = '2019'
    group by
        age, gender_code, pf_code
)
select
    AD.age as AGE,
    case when AD.gender_code = '20' then 'MALE' else 'FEMALE' end as GENDER,
    A.avg_height as H_AVG,
    printf(
        "%.1f%",
        round(
            sum(case when AD.height >= A.avg_height then 1 else 0 end) / (1.0 * sum(AD.height > 0)) * 100,
            1
        )
    ) as H_PER,
    A.avg_weight as W_AVG,
    printf(
        "%.1f%",
        round(
            sum(case when AD.weight >= A.avg_weight then 1 else 0 end) / (1.0 * sum(AD.weight > 0)) * 100,
            2
        )
    ) as W_PER
from
    averages_district AD
    inner join averages A on
        AD.age = A.age and
        AD.gender_code = A.gender_code
group by
    AD.age,
    AD.gender_code
order by
    AD.age desc,
    AD.gender_code desc
;