with GROUP_TBL(PF_CODE, GROUP_CODE) as (
    select
        PF_CODE
        , (RANK_NO - 1) / 3
    from
        (select
            PF_CODE
            , row_number () over (order by TOTAL_VALUE desc) as RANK_NO
        from
            CONVENIENCE
        where
            SURVEY_YEAR=2019
            and KIND_CODE='100'
        )
), GROUP_SUMMERY(GROUP_CODE, TTL_SAL, CNT_STORE) as (
    select
        G.GROUP_CODE+1
        , 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_CODE
)

select
    G.GROUP_CODE 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_CODE 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(case
                when KIND_CODE='100' then TOTAL_VALUE
                else 0
            end) as ALL_TTL_SAL
        from
            CONVENIENCE
        where
            SURVEY_YEAR=2019
        ) as S
order by
    NO asc
;