with base as (
select ITEM_CODE, 
sum(SALES_QTY * UNITPRICE ) as AMT,
rank()over(order by sum(SALES_QTY * UNITPRICE ) desc) as rnk
from SALES
join SALES_DTL
on   SALES.SALES_NO = SALES_DTL.SALES_NO
where SALES.SALES_DATE between '2023-06-01' and '2023-06-30'
group by ITEM_CODE
)
select main.ITEM_CODE as CODE,
(select ITEM_NAME from ITEM where ITEM_CODE = main.ITEM_CODE) as NAME,
main.AMT as SAL_AMT,
(select sum(AMT) from base where rnk <= main.rnk) as CML_AMT,
round(cast (main.AMT as real) *100 / (select sum(AMT) from base) , 1) || '%' as SAL_COMP,
round(cast ((select sum(AMT) from base where rnk <= main.rnk) as real) *100 / (select sum(AMT) from base) , 1) || '%' as TTL_COMP,
case 
when round(cast ((select sum(AMT) from base where rnk <= main.rnk) as real) *100 / (select sum(AMT) from base) , 1)  <= 40 then 'A'
when round(cast ((select sum(AMT) from base where rnk <= main.rnk) as real) *100 / (select sum(AMT) from base) , 1)  <= 80 then 'B'
else 'C' end as RANK
from base main