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
    *
    ,TRUNC((RN-1)/3)+1  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