with avg_tbl as (
    select
        AGE
        ,GENDER_CODE
        ,CATEGORY_CODE
        ,round(avg(AVERAGE_VALUE),1) total_avg
        ,count(PF_CODE) total_cnt
    from
        SCHOOL_HEALTH
    where 
        SURVEY_YEAR = 2019
    group by 
        AGE
        ,GENDER_CODE
        ,CATEGORY_CODE
)
, cnt_tbl as (
    select 
        a.AGE
        ,a.GENDER_CODE
        ,a.CATEGORY_CODE
        ,max(b.total_avg) as total_avg
        ,cast(max(b.total_cnt) as real) as total_cnt
        ,cast(count(*) as real) as cnt
    from SCHOOL_HEALTH a
        inner join avg_tbl b
        on a.AGE = b.AGE
        and a.GENDER_CODE = b.GENDER_CODE
        and a.CATEGORY_CODE = b.CATEGORY_CODE
    where 
        a.AVERAGE_VALUE >= b.total_avg
    group by
        a.AGE
        ,a.GENDER_CODE
        ,a.CATEGORY_CODE
)
, h_tbl as (
    select
        AGE
        ,GENDER_CODE
        ,CATEGORY_CODE
        ,total_avg as AVG
        ,round(cast(cnt / total_cnt * 100 as real),1) || '%' as PER
    from cnt_tbl
    where CATEGORY_CODE = '10'
)
, w_tbl as (
    select
        AGE
        ,GENDER_CODE
        ,CATEGORY_CODE
        ,total_avg as AVG
        ,round(cast(cnt / total_cnt * 100 as real),1) || '%' as PER
    from cnt_tbl
    where CATEGORY_CODE = '20'
)
select 
    h.AGE
    ,case when h.GENDER_CODE = '20' then 'MALE' else 'FEMALE' end as GENDER
    ,h.AVG as H_AVG
    ,h.PER as H_PER
    ,w.AVG as W_AVG
    ,w.PER as W_PER
from h_tbl as h
    left join w_tbl as w
    on h.AGE = w.AGE
    and h.GENDER_CODE = w.GENDER_CODE
order by
    h.AGE desc
    ,h.GENDER_CODE desc