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
        ,case when GENDER_CODE = '20' then 'MALE' else 'FEMALE' end as GENDER
        --,CATEGORY_CODE
        ,total_avg as H_AVG
        ,round(cast(cnt / total_cnt * 100 as real),1) || '%' as H_PER
        
    from cnt_tbl
    where CATEGORY_CODE = '10'
)
, w_tbl as (
    select
        AGE
        ,case when GENDER_CODE = '20' then 'MALE' else 'FEMALE' end as GENDER
        --,CATEGORY_CODE
        ,total_avg as W_AVG
        ,round(cast(cnt / total_cnt * 100 as real),1) || '%' as W_PER
    from cnt_tbl
    where CATEGORY_CODE = '20'
)
,disp_tbl as (
    select 
        h.AGE
        ,h.GENDER
        ,H_AVG
        ,H_PER
        ,W_AVG
        ,W_PER
    from h_tbl as h
        left join w_tbl as w
        on h.AGE = w.AGE
        and h.GENDER = w.GENDER
)
select 
        AGE
        ,GENDER
        ,H_AVG
        ,H_PER
        ,W_AVG
        ,W_PER
from disp_tbl
order by
    AGE desc
    ,GENDER