with
  a as (
    select
      pf_code,
      han,
      ten,
      (
        rank() over (
          order by
            han desc,
            ten,
            pf_code
        ) -1
      ) / 3 as rnk
    from
      (
        select
          PF_CODE,
          sum(
            case KIND_CODE
              when '100' then TOTAL_VALUE
              else 0
            end
          ) as han,
          sum(
            case KIND_CODE
              when '150' then TOTAL_VALUE
              else 0
            end
          ) as ten
        from
          CONVENIENCE
        where
          SURVEY_YEAR = 2019
        group by
          PF_CODE
      )
  ),
  b as (
    select
      sum(TOTAL_VALUE) as hansum
    from
      CONVENIENCE
    where
      SURVEY_YEAR = 2019
      and kind_code = '100'
  )
select
  NO,
  CUM_SAL as PER_SAL,
  AVG_SAL,
  round(
    cast(
      sum(TTL_SAL) over (
        order by
          NO
      ) as real
    ) * 100 / cast(b.hansum as real),
    1
  ) as CUM_SAL,
  TTL_SAL
from
  (
    select
      a.rnk + 1 as NO,
      sum(a.han) TTL_SAL,
      round(
        cast(sum(a.han) as real) * 100 / cast(max(b.hansum) as real),
        1
      ) CUM_SAL,
      sum(a.han) / sum(a.ten) AVG_SAL
    from
      a,
      b
    group by
      a.rnk
  ),
  b