with GROUP_TBL(PF_CODE, GROUP_NO) as (
    select
        C.PF_CODE
        , (row_number () over (order by S.SAL_AMT desc, C.TOTAL_VALUE asc, C.PF_CODE asc) - 1) / 3 + 1
    from
        CONVENIENCE as C
    left join
        (
            select
                PF_CODE
                , TOTAL_VALUE as SAL_AMT
            from
                CONVENIENCE
            where 
                SURVEY_YEAR = 2019
                and KIND_CODE = '100'
        ) as S
        on C.PF_CODE=S.PF_CODE
    where
        C.SURVEY_YEAR = 2019
        and C.KIND_CODE = '150'
), GROUP_SUMMERY(GROUP_NO, TTL_SAL, CNT_STORE) as (
    select 
        G.GROUP_NO
        , cast(
            sum(
                case
                    when C.KIND_CODE = '100' then TOTAL_VALUE
                    else 0
                end
            ) as real
        )
        , cast(
            sum(
                case
                    when C.KIND_CODE = '150' then TOTAL_VALUE
                    else 0
                end
            ) as real
        )
    from GROUP_TBL as G
        left join CONVENIENCE as C on G.PF_CODE = C.PF_CODE
    where SURVEY_YEAR = 2019
    group by G.GROUP_NO
)
select G.GROUP_NO as NO,
    G.TTL_SAL
    , round((G.TTL_SAL / S.ALL_TTL_SAL) * 100, 1) as PER_SAL
    , round(
        (
            sum(G.TTL_SAL) over (
                order by G.GROUP_NO asc rows between unbounded preceding and current row
            ) / S.ALL_TTL_SAL
        ) * 100,
        1
    ) as CUM_SAL
    , cast((G.TTL_SAL / G.CNT_STORE) as int) as AVG_SAL
from GROUP_SUMMERY as G,
    (
        select 
            sum(TOTAL_VALUE) as ALL_TTL_SAL
        from CONVENIENCE
        where 
            SURVEY_YEAR = 2019
            and KIND_CODE = '100'
    ) as S
order by NO asc;