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 
  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_3_sum as 
(
  select 
    NO 
    ,sum(sales) as sales 
    ,sum(shop_count) as shop_count
  from tmp_3 
  group by 1 
)
-- , tmp_cum as 
, cum as 
(
  select 
   a.NO 
--   ,a.sales 
   ,sum(b.sales) as cum_sales
  from tmp_3_sum as a 
  inner join tmp_3_sum as b 
    on a.NO >= b.NO 
  group by 1
)

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 
  ,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 
;