with base as (
select
    t1.PF_CODE
    , t1.TOTAL_VALUE as SALE
    , t2.TOTAL_VALUE as SHOP
from
    CONVENIENCE as t1
    inner join
    CONVENIENCE as t2
    on t1.PF_CODE=t2.PF_CODE
    and t1.KIND_CODE = 100
    and t2.KIND_CODE = 150
    and t1.SURVEY_YEAR = 2019
    and t2.SURVEY_YEAR = 2019
)
,base2 as (
select
    PF_CODE
    ,SALE
    ,SHOP
    ,(row_number() over (order by SALE desc,SHOP,PF_CODE)-1)/3+1 as NO
    ,sum(SALE) over () as TOTAL
from
    base
)
,base3 as (
select
    NO
    ,TOTAL
    ,sum(SALE) as TTL_SAL
    ,sum(SHOP) as SHOP
from
    base2
group by
    NO
    ,TOTAL
)
select
    NO
    ,TTL_SAL
    ,round(100.0*TTL_SAL/TOTAL,1) as PER_SAL
    ,round(100.0*sum(TTL_SAL) over(order by NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /TOTAL,1) as CUM_SAL
    ,round(1.0*TTL_SAL/SHOP,0) as AVG_SAL
from
    base3
order by
    NO