with 


CLASS as (
select
    P.pf_code
    ,ntile(10) over (order by total_value desc, stores, P.pf_code) as class
from 
    CONVENIENCE P
    left outer join
        (select
            pf_code
            ,total_value as stores
        from
            CONVENIENCE
        where
            survey_year='2019'
            and kind_code='150'
        ) S 
        on P.pf_code=S.pf_code
where
    survey_year='2019'
    and kind_code='100'
)
, C as (
select
    survey_year
    ,P.pf_code
    ,kind_code
    ,total_value
    ,class
from
    CONVENIENCE P
    left outer join
        CLASS S 
        on P.pf_code = S.pf_code
where
    survey_year='2019'
)
,C1 as (
select distinct
    class
    ,sum(total_value) over (partition by CLASS) as SUM1
    ,sum(total_value) over () as SUM2
from
    C
where
    kind_code='100'
)
,C2 as (
select
    P.class
    ,P.SUM1 as TTL_SAL
    ,100.0*P.SUM1/P.SUM2 as PER_SAL
    ,100.0*sum(S.SUM1)/P.SUM2 as CUM_SAL
from
    C1 P
    left outer join
        C1 S 
        on P.class>=S.class
group by 1,2,3
)
,C3 as (
select
    class
    ,sum(case when kind_code='100' then total_value end)
    /sUM(case when kind_code='150' then total_value end) as AVG_SAL
from 
    C
group by 1
)
select
    C2.class as NO
    ,TTL_SAL
    ,round(PER_SAL, 1) as PER_SAL
    ,round(CUM_SAL, 1) as CUM_SAL
    ,AVG_SAL
from
    C2
        inner join C3
        on C2.class = C3.class
;