with NOW as(
select 
SD.ITEM_CODE as CODE
,IT.ITEM_NAME as NAME
,SD.UNITPRICE*sum(SD.SALES_QTY) as SAL_AMT
,sum(SD.UNITPRICE*sum(SD.SALES_QTY)) over (ORDER BY (SD.UNITPRICE*sum(SD.SALES_QTY)) DESC)  as CML_AMT
from SALES_DTL SD
    inner join SALES SA 
        on SD.SALES_NO=SA.SALES_NO
        and SA.SALES_DATE between '2023-06-01' and '2023-06-30'
    inner join ITEM IT on SD.ITEM_CODE=IT.ITEM_CODE
group by CODE 
order by CODE desc
)

select
NOW.CODE
,NOW.NAME
,NOW.SAL_AMT
,NOW.CML_AMT
,round(cast(SAL_AMT as real)*100/cast(MAX as real),1)||'%' as SAL_COMP
,round(cast(CML_AMT as real)*100/cast(MAX as real),1) ||'%'as TTL_COMP
,case when round(cast(CML_AMT as real)*100/cast(MAX as real),1)>80 then 'C' 
    when round(cast(CML_AMT as real)*100/cast(MAX as real),1)>40 then 'B' 
    else 'A' end as RANK 
from NOW
cross join (select max(CML_AMT) as MAX from NOW)
group by CODE 
order by SAL_AMT desc,CODE desc