with horizon as (
    select 
        pf_code,
        sum(case kind_code when '100' then total_value else 0 end) sales,
        sum(case kind_code when '150' then total_value else 0 end) stores
    from convenience
    where survey_year = 2019
    group by pf_code
),
total_sales as (select sum(sales) from horizon),
ntiled_stat as (
    select ntile(10) over (order by sales desc, stores desc, pf_code asc) no,
        pf_code, sales, stores
    from horizon
),
grouped_stat as (
    select no, pf_code, sum(sales) sum_sales, sum(stores) sum_stores
    from ntiled_stat
    group by no
)
select no NO,
    sum_sales TTL_SAL,
    round(100.0 * sum_sales / (select * from total_sales), 1) PER_SAL,
    round(100.0 * sum(sum_sales) over (order by no) / (select * from total_sales), 1) CUM_SAL,
    sum_sales / sum_stores AVG_SAL
from grouped_stat