with tmp_1 as 
(
  select 
   pf_code 
   ,sum(case when kind_code = '100' then total_value end) as sales
   ,sum(case when kind_code = '150' then total_value end) as shop_count 
  from convenience 
  where survey_year = 2019
  group by 1 
--   order by sales desc, shop_count, pf_code 
)
, tmp_2 as 
(
  select 
    pf_code 
    ,sales 
    ,shop_count 
    -- ,row_number() over (order by sales desc, shop_count, pf_code) as rank 
    ,rank() over (order by sales desc, shop_count, pf_code) as rank 
  from tmp_1 
)
, tmp_3 as 
(
  select 
    pf_code 
    ,sales 
    ,shop_count 
    ,rank 
    ,ceiling(cast(rank as real) / 3) as NO
  from tmp_2 
)
, tmp_cum as 
(
  select 
    a.NO 
    ,a.sales 
    ,sum(b.sales) as cum_sales 
  from tmp_3 as a 
  inner join tmp_3 as b 
    on a.NO >= b.NO  
  group by a.NO, a.sales 
  order by a.NO 
)
,cum as 
(
  select 
    NO 
    ,cum_sales
  from tmp_cum 
  group by 1,2 
)
select 
  tmp_3.NO 
  ,sum(sales) as TTL_SAL 
  ,round(100 * cast(sum(sales) as real) / (select sum(sales) from tmp_1), 1) as PER_SAL 
--   ,round(100.0 * c.cum_sales /  (select sum(sales) from tmp_1), 1) as CUM_SAL 
  ,round(100 * cast(c.cum_sales as real) / (select sum(sales) from tmp_1), 1) as CUM_SAL 
--   ,floor(cast(sum(sales) as real) / sum(shop_count)) as AVG_SAL
  ,cast(cast(sum(sales) as real) / sum(shop_count) as int) as AVG_SAL
from tmp_3 
inner join cum as c 
  on tmp_3.NO = c.NO
group by tmp_3.NO 
;