with SAL_AMT as(
select
ITEM.ITEM_CODE,
ITEM.ITEM_NAME,
sum(UNITPRICE * SALES_QTY) as SAL_AMT
from
SALES
inner join
SALES_DTL
on SALES.SALES_NO = SALES_DTL.SALES_NO
inner join
ITEM
on SALES_DTL.ITEM_CODE = ITEM.ITEM_CODE
where
SALES_DATE between '2023-06-01' and '2023-06-30'
group by ITEM.ITEM_CODE
order by SAL_AMT desc),


SAL_COMP as(
select
rank() over(order by SAL_AMT desc) as NUM,
ITEM_CODE,
ITEM_NAME,
SAL_AMT,
sum(SAL_AMT) over() as TOTAL_SAL_AMT,
cast(SAL_AMT as real) / cast(sum(SAL_AMT) over() as real) *100 as SAL_COMP
from SAL_AMT
order by SAL_COMP desc
,ITEM_CODE desc
)
select
A.ITEM_CODE as CODE,
A.ITEM_NAME as NAME,
A.SAL_AMT,
sum(B.SAL_AMT) as CML_AMT,
round(A.SAL_COMP,1) || '%' as SAL_COMP,
round(sum(B.SAL_COMP),1) || '%' as TTL_COMP,
case when sum(B.SAL_COMP) <= 40 then 'A'
when sum(B.SAL_COMP) <= 80 then 'B'
else 'C' end as RANK
from SAL_COMP A
inner join
SAL_COMP as B
on A.NUM >= B.NUM
group by 
A.NUM,
A.ITEM_CODE,
A.ITEM_NAME,
A.TOTAL_SAL_AMT
order by
A.SAL_COMP desc
,A.ITEM_CODE desc