with SALES_DATA as (
  select
    sd.ITEM_CODE as CODE,
    it.ITEM_NAME as NAME,
    sum(sd.UNITPRICE * sd.SALES_QTY) as SAL_AMT,
    sum(sum(sd.UNITPRICE * sd.SALES_QTY)) over(order by sum(sd.UNITPRICE * sd.SALES_QTY) desc) as CML_AMT,
    round(
      cast(sum(sd.UNITPRICE * sd.SALES_QTY) as float) * 100 /
      sum(sum(sd.UNITPRICE * sd.SALES_QTY)) over(rows between unbounded preceding and unbounded following),
      1
    ) || ' %' as SAL_COMP,
    round(
      sum(sum(cast(sd.UNITPRICE as float) * sd.SALES_QTY) * 100) over(order by sum(sd.UNITPRICE * sd.SALES_QTY) desc) /
      sum(sum(sd.UNITPRICE * sd.SALES_QTY)) over(),
      1
    ) as TTL_COMP_RAW
  from SALES_DTL as sd
  left join SALES as s using(SALES_NO)
  left join ITEM as it using(ITEM_CODE)
  where date(s.SALES_DATE) between '2023-06-01' and '2023-06-30'
  group by sd.ITEM_CODE, it.ITEM_NAME
  order by SAL_AMT desc
)
select
  CODE,
  NAME,
  SAL_AMT,
  CML_AMT,
  SAL_COMP,
  TTL_COMP_RAW || ' %' as TTL_COMP,
  case
    when TTL_COMP_RAW <= 40 then 'A'
    when TTL_COMP_RAW <= 80 then 'B'
    else 'C'
  end as RANK
from SALES_DATA
order by 3 desc,1 desc
;