with rnktbl as (select item_code,
                       sum(unitprice * sales_qty) uri
                from SALES
                         inner join SALES_DTL as dtl on sales.sales_no = dtl.sales_no
                where sales_date between '2023-06-01' and '2023-06-30'
                group by item_code
                order by 2 desc),
     tot as (select sum(unitprice * sales_qty)               v,
                    cast(sum(unitprice * sales_qty) as real) fv
             from SALES
                      inner join SALES_DTL as dtl on sales.sales_no = dtl.sales_no
             where sales_date between '2023-06-01' and '2023-06-30')

select rnktbl.item_code                             CODE,
        item.item_name NAME,
       max(rnktbl.uri)                              SAL_AMT,
       sum(t2.uri)                                  CML_AMT,
       round(cast(max(rnktbl.uri) as real) / tot.fv*100,1)||'%' SAL_COMP,
       round(cast(sum(t2.uri) as real) / tot.fv*100,1)||'%'     TTL_COMP,
       case when round(cast(sum(t2.uri) as real) / tot.fv*100,1) <=40.0 then 'A'
           when round(cast(sum(t2.uri) as real) / tot.fv*100,1) <= 80.0 then 'B'
           else 'C' end RANK
from rnktbl
         inner join rnktbl t2 on rnktbl.uri <= t2.uri
   inner join item on item.item_code=rnktbl.item_code
   , tot

group by rnktbl.item_code, tot.fv
         order by 3 desc,1 desc;