with total_sal as (
select sum(total_value) as total_amt
from convenience
where survey_year=2019
and   kind_code='100'
),
decile_group as (
select 
    c1.pf_code
    , c1.total_value as total_sal
    , c2.total_value as total_shop
    , ntile(10) over (order by c1.total_value desc, c2.total_value asc, c1.pf_code asc) as dc_no
from convenience as c1
inner join convenience as c2 on c1.pf_code=c2.pf_code
                            and c2.survey_year=2019
                            and c2.kind_code='150'
where c1.survey_year=2019
and   c1.kind_code='100'
),
dc_summary as (
select 
    dc_no
    , sum(total_sal) as dc_sal
    , sum(total_shop) as dc_shop
from decile_group
group by dc_no
)
select 
    ds.dc_no as NO
    , ds.dc_sal as TTL_SAL
    , round((cast(ds.dc_sal as real)/ts.total_amt *100),1) as PER_SAL
    , round((cast(sum(ds.dc_sal) over (order by ds.dc_no asc) as real)/ts.total_amt *100),1) as CUM_SAL
    , cast((cast(ds.dc_sal as real)/ds.dc_shop) as int) as AVG_SAL
from dc_summary ds
cross join total_sal ts
order by ds.dc_no asc;