with item_sum as (
    select
        d.ITEM_CODE as CODE
      , i.ITEM_NAME as NAME
      , sum(d.UNITPRICE * d.SALES_QTY) as SAL_AMT
    from
               SALES     s
    inner join SALES_DTL d using(SALES_NO)
    inner join ITEM      i using(ITEM_CODE)
    where
        '2023-06-01' <= s.SALES_DATE and s.SALES_DATE <= '2023-06-30'
    group by
        d.ITEM_CODE

), total_sum as (
    select
        sum(SAL_AMT) as TOTAL_AMT
    from
        item_sum

), cum_sum as (
    select
        CODE
      , NAME
      , SAL_AMT
      , TOTAL_AMT
      , SUM(SAL_AMT) over(order by SAL_AMT desc) as CML_AMT
    from
        item_sum
      , total_sum

), proportion as (
    select
        CODE
      , NAME
      , SAL_AMT
      , CML_AMT
      , round((SAL_AMT * 100 * 1.00 / TOTAL_AMT * 1.00) , 2) as SAL_COMP
      , round((CML_AMT * 100 * 1.00 / TOTAL_AMT * 1.00) , 2) as TTL_COMP
    from
        cum_sum
)
select
    CODE
  , NAME
  , SAL_AMT
  , CML_AMT
  , format('%2.1f％', SAL_COMP) as SAL_COMP
  , format('%2.1f％', TTL_COMP) as TTL_COMP
  , case
        when TTL_COMP <= 40 then 'A'
        when TTL_COMP <= 80 then 'B'
        else                     'C'
    end as RANK
from
    proportion
order by
    3 desc
  , 1 desc
;