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
    ,(rank + 2) / 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 
-- )
, tmp_cum as 
(
  select 
    NO 
    ,sales 
    ,sum(sales) over (order by NO) as cum_sales
  from tmp_3 
)
,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 
;