with HAN as (
select c.*
, sum(C.TOTAL_VALUE) over() SUM_HAN
from CONVENIENCE C
where C.SURVEY_YEAR = 2019
and C.KIND_CODE = '100'
), TEN as (
select c.*
from CONVENIENCE C
where C.SURVEY_YEAR = 2019
and C.KIND_CODE = '150'
), N as (
select H.PF_CODE
, H.TOTAL_VALUE HAN
, H.SUM_HAN
, T.TOTAL_VALUE TEN
, rank() over(order by H.TOTAL_VALUE desc, T.TOTAL_VALUE asc, H.PF_CODE asc)/3
  + case when mod(rank() over(order by H.TOTAL_VALUE desc, T.TOTAL_VALUE asc, H.PF_CODE asc),3) = 0 then 0 else 1 end NO
from HAN H
     inner join TEN T
       on T.PF_CODE = H.PF_CODE
       
)
select N.NO
, sum(N.HAN) TTL_SAL
, round(cast(SUM(N.HAN) as real)/cast(min(N.SUM_HAN) as cast)  * 100,1) PER_SAL
, round(cast((select sum(NS.HAN) from N NS where NS.NO <= N.NO) as real)/cast(min(N.SUM_HAN) as cast)  * 100,1) CUM_SAL

, cast(cast(sum(N.HAN) as real)/cast(sum(N.TEN) as real) as int) AVG_SAL

from N
group by N.NO
order by N.NO
;