with s as (
  select 
    i.ITEM_CODE CODE,
    i.ITEM_NAME NAME,
    sum(o.unitprice * o.sales_qty) TOTAL_AMT
  from sales_dtl o
  left join item as i using(item_code)
  left join sales as s using(sales_no)
  where '2023-06-01' <= s.sales_date and s.sales_date <= '2023-06-30'
  group by 1,2
)
, ss as (
   select s.*, t.total, 
   100.0 * s.total_amt / t.total SAL_COMP
   from s
   cross join (select sum(TOTAL_AMT) total from s) t
)
, ss_cum as (
   select 
    s1.*, 
    sum(s2.total_amt) CML_AMT, 
    100.0 * sum(s2.total_amt)/s1.total TTL_COMP
   from ss as s1
   cross join ss as s2
   where (s1.SAL_COMP <= s2.SAL_COMP)
   group by 1,2,3,4,5
)
select 
  c.code,
  c.name,
  c.total_amt as SAL_AMT,
  c.cml_amt as CML_AMT, 
  format('%.1f%%', c.sal_comp) SAL_COMP,
  format('%.1f%%', c.ttl_comp) TTL_COMP,
  CASE 
    when c.ttl_comp <= 40 then 'A'
    when c.ttl_comp > 80 then 'C'
    else 'B'
  END as RANK
from ss_cum c
order by sal_amt desc, code desc