with master as(
    select
        pf_code
        ,sum(case when kind_code= '100' then total_value end) as '販売額' 
        ,sum(case when kind_code= '150' then total_value end) as '店舗数' 
    from
        convenience
    where
        SURVEY_YEAR=2019
    group by
        pf_code
    )
,master2 as(
select
    *
    ,rank()over(order by 販売額 desc,店舗数 asc,pf_code asc)as rn
from
    master
),master3 as(
select
    *
    ,case 
        when rn in (1,2,3) then 1 
        when rn in (4,5,6) then 2 
        when rn in (7,8,9) then 3
        when rn in (10,11,12) then 4 
        when rn in (13,14,15) then 5 
        when rn in (16,17,18) then 6 
        when rn in (19,20,21) then 7 
        when rn in (22,23,24) then 8 
        when rn in (25,26,27) then 9 
        when rn in (28,29,30) then 10
    end as NO
from
    master2
)
, master4 as(
    select
        NO
        ,ROUND(CAST(sum(販売額) AS REAL),1)as 販売額
        ,SUM(店舗数)AS 店舗数
    from
        master3
    group by
        NO
)
select
    no
    ,販売額 as TTL_SAL
    ,ROUND(100*販売額/SUM(販売額) OVER() ,1) AS PER_SAL
    ,ROUND(100*SUM(販売額)OVER(ORDER BY NO ASC)/SUM(販売額) OVER(),1) AS CUM_SAL
    ,FLOOR(販売額/店舗数)AS AVG_SAL
from
    master4