with target_year_sales as (
  select
      PF_CODE
    , TOTAL_VALUE as sales
  from
      CONVENIENCE c1
  where
      SURVEY_YEAR = '2019'
  and KIND_CODE   = '100'

), target_year_tenpo as (
  select
      PF_CODE
    , TOTAL_VALUE as tenpo
  from
      CONVENIENCE c1
  where
      SURVEY_YEAR = '2019'
  and KIND_CODE   = '150'

), sales_ranking as (
  select
      rank() over(ORDER BY sales desc, tenpo asc, PF_CODE asc) as sales_rank
    , PF_CODE
    , sales
    , tenpo
  from
             target_year_sales s
  inner join target_year_tenpo t using(PF_CODE)

), grouping as (
  select
      floor((sales_rank - 1) / 3) + 1 as NO
    , PF_CODE
    , sales
    , tenpo
  from
      sales_ranking

), group_summary as (
  select
      NO
    , sum(sales) as TTL_SAL
    , sum(tenpo) as TTL_TEN
  from
      grouping
  group by
      NO

), prepare_output as (
  select
      NO
    , TTL_SAL
    , sum(TTL_SAL) over(order by NO asc) as CUM_SAL
    , TTL_TEN
    , sum(TTL_SAL) over() as WHOLE_TTL_SAL 
  from
      group_summary

)
select
    NO
  , TTL_SAL                         as TTL_SAL
  , round(TTL_SAL * 100.0 / WHOLE_TTL_SAL, 1) as PER_SAL
  , round(CUM_SAL * 100.0 / WHOLE_TTL_SAL, 1) as CUM_SAL
  , TTL_SAL         / TTL_TEN       as AVG_SAL
from
    prepare_output
order by
    NO asc
;