with avg_ as 
(
  select 
    gender_code 
    ,age 
    ,category_code 
    ,round(avg(average_value),1) as avg_value
  from school_health 
  where survey_year = 2019 
  group by 1,2,3
)
select 
  s.*
from school_health as s 
inner join avg_ as a 
  on s.gender_code = a.gender_code 
  and s.age = a.age 
  and s.category_code = a.category_code 
-- where s.average_value >= a.avg_value 
;


with avg_ as 
(
  select 
    gender_code 
    ,age 
    ,category_code 
    ,round(avg(average_value),1) as avg_value
  from school_health 
  where survey_year = 2019 
  group by 1,2,3
)
,h as 
(
  select 
    s.age as AGE 
    ,case when s.gender_code = '20' then 'MALE'
      when s.gender_code = '30' then 'FEMALE'
      end as GENDER 
    ,case when s.category_code = '10' then a.avg_value
      end as H_AVG 
    ,round(100.0 * sum(case when s.average_value >= a.avg_value then 1 else 0 end) / count(distinct s.pf_code),1) || '%' as H_PER
  from school_health as s 
  inner join avg_ as a 
    on s.gender_code = a.gender_code 
    and s.age = a.age 
    and s.category_code = a.category_code 
  where s.survey_year = 2019
  group by 1,2,3
  having H_AVG is not null 
)
,w as 
(
  select 
    s.age as AGE 
    ,case when s.gender_code = '20' then 'MALE'
      when s.gender_code = '30' then 'FEMALE'
      end as GENDER 
    ,case when s.category_code = '20' then a.avg_value
      end as W_AVG 
    ,round(100.0 * sum(case when s.average_value >= a.avg_value then 1 else 0 end) / count(distinct s.pf_code),1) || '%' as W_PER
  from school_health as s 
  inner join avg_ as a 
    on s.gender_code = a.gender_code 
    and s.age = a.age 
    and s.category_code = a.category_code 
  where s.survey_year = 2019
  group by 1,2,3
  having W_AVG is not null 
)
-- select
--  *
-- from h 
-- inner join w 
--   on h.AGE = w.AGE 
--   and h.GENDER = w.GENDER


select 
  h.AGE 
  ,h.GENDER 
  ,h.H_AVG 
  ,h.H_PER 
  ,w.W_AVG 
  ,w.W_PER
from h 
inner join w 
  on h.AGE = w.AGE 
  and h.GENDER = w.GENDER
order by h.AGE desc, h.GENDER
;