with CLASS as (
select
    pf_code
    ,ntile(10) over (order by total_value desc) as class
from 
    CONVENIENCE
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
;