with tmp as
(
select sum(UNITPRICE * SALES_QTY) SAL_AMT,
item.ITEM_CODE
from sales inner join sales_dtl on sales.sales_no=sales_dtl.sales_no
inner join item on item.item_code=sales_dtl.item_code
where SALES_DATE between '2023-06-01' and '2023-06-30'
group by 2 order by 1 desc
),
 tmp2 as
(select sum(SAL_AMT) as totals from tmp),
 tmp3 as
(
select ITEM_CODE,SAL_AMT, 100.0*SAL_AMT/totals
as SAL_COMP
, rank() over (partition by SAL_AMT) ranked
from tmp , tmp2
),
tmp4 as (
select ITEM_CODE,SAL_AMT,
sum(SAL_AMT) over (partition by ranked order by SAL_COMP desc,SAL_AMT desc ) as CML_AMT ,
SAL_COMP  as SAL_COMP,
sum(SAL_COMP) over (partition by ranked order by SAL_COMP desc,SAL_AMT desc )  as TTL_COMP 
from tmp3
)
select 
tmp4.ITEM_CODE as CODE,item_name as NAME,SAL_AMT,CML_AMT,round(SAL_COMP,1)||'%' as SAL_COMP,round(TTL_COMP,1)||'%' as TTL_COMP
,
CASE
when TTL_COMP<=40 THEN 'A'
When  TTL_COMP<=80 THEN 'B'
ELSE 'C' 
END as RANK
from tmp4
inner join 
item
on tmp4.item_code=item.item_code
order by 
SAL_AMT desc,tmp4.ITEM_CODE desc