with sa as (
    select 
        row_number() over(order by sum(total_value) desc) as ranking,
        pf_code,
        sum(total_value) as sales,
        sum(total_value) over() as sumtotal
    from convenience
    where survey_year='2019' and kind_code='100'
    group by pf_code
),
cnt as (
    select 
        pf_code,
        sum(total_value) as count
    from convenience
    where survey_year='2019' and kind_code='150'
    group by pf_code
),
sumtbl as (
    select
        ranking,
        sa.pf_code as code,
        sales,
        count,
        sum(sales) over(order by ranking rows 2 preceding) as TTL_SAL,
        sum(sales) over(order by ranking) as subtotal,
        sumtotal,
        sum(count) over(order by ranking rows 2 preceding) as gsum_shop
    from sa
    inner join cnt
    on sa.pf_code=cnt.pf_code
    order by sales desc,count,code
)
select 
    ranking/3 as NO,
    TTL_SAL,
    round(cast(TTL_SAL as real)/cast(sumtotal as real)*100,1) as PER_SAL,
    round(cast(subtotal as real)/cast(sumtotal as real)*100,1) as CUM_SAL,
    cast(TTL_SAL/gsum_shop as int) as AVG_SAL
from sumtbl
where ranking%3=0
order by NO;