with
    --販売額と店舗数を別のカラムに落とし込み、同一のテーブル（sub）へ
    sub_SAL as (
        select
            SURVEY_YEAR
            ,PF_CODE
            ,TOTAL_VALUE as SAL
        from 
            CONVENIENCE
        where
            KIND_CODE = '100'
        )
    ,sub_T as (
        select
            SURVEY_YEAR
            ,PF_CODE
            ,TOTAL_VALUE as T
        from 
            CONVENIENCE
        where
            KIND_CODE = '150'
        )
    ,sub_2019 as (
        select
            sub_SAL.SURVEY_YEAR as SURVEY_YEAR
            ,sub_SAL.PF_CODE as PF_CODE
            ,sub_SAL.SAL as SAL
            ,sub_T.T as T
            ,ntile(10) over (Partition by sub_SAL.SURVEY_YEAR order by SAL desc,T asc,sub_SAL.PF_CODE asc) as SAL_gp
        from
            sub_SAL
            inner join sub_T
                on sub_SAL.PF_CODE = sub_T.PF_CODE
                and sub_SAL.SURVEY_YEAR = sub_T.SURVEY_YEAR
        where sub_SAL.SURVEY_YEAR='2019'
        )
--select * from sub_2019

    ,MUS_2019 as (
        select 
            sum(SAL) as SAL_2019
        from
            sub_2019
        )
--select * from MUS_2019
    ,sub as (
        select
            sub_2019.SAL_gp as NO
            ,SUM(SAL) as TTL_SAL
            ,round(SUM(SAL)*100.0 / SAL_2019,1) as PER_SAL
            ,SUM(SAL)/SUM(T) as AVG_SAL
        from 
            sub_2019
            join MUS_2019
        group by SAL_gp
        order by NO asc
        )
--select * from sub

select
    NO
    ,TTL_SAL
    ,PER_SAL
    ,round(SUM(TTL_SAL)over(order by NO)*100.0/ SAL_2019,1) as CUM_SAL
    ,AVG_SAL
from sub
    left outer join MUS_2019
order by NO asc