with
    yoko as (
        select
            pf_code
            ,sum(case when kind_code = 100 then total_value end) as sales
            ,sum(case when kind_code = 150 then total_value end) as shops
        from
            convenience
        where
            survey_year = 2019
        group by
            1
        order by
            2 desc, 3 asc, 1 asc
    )
    ,rank as (
        select
            (row_number() over() + 2) / 3 as rank
            ,pf_code
            ,sales
            ,shops
        from
            yoko
    )
    ,ttl as (
        select
            rank
            ,sum(sales) as sales
            ,sum(shops) as shops
        from
            rank
        group by
            1
        order by
            1
    )
select
    rank as NO
    ,sales as TTL_SAL
    ,round(cast(sales as real) * 100 / cast(sum(sales) over() as real), 1) as PER_SAL
    ,round(cast(sum(sales) over(order by rank) as real) * 100 / cast(sum(sales) over() as real), 1) as CUM_SAL
    ,cast(cast(sales as real) / cast(shops as real) as int) as AVG_SAL
from
    ttl
group by
    1
order by
    1