with target as (
select i.item_code as CODE,
item_name as NAME,
sum (d.UNITPRICE * d.SALES_QTY) as SAL_AMT
from sales s, SALES_DTL d, item i
where
 i.item_code=d.item_code 
and  d.SALES_NO=s.SALES_NO
and  s.SALES_DATE between '2023-06-01' and '2023-06-30'
group by CODE,NAME
),
all_amt as (
 select sum(SAL_AMT) all_amt from target
)
-- select all_amt from all_amt
select CODE,NAME,SAL_AMT,
SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc ) AS CML_AMT,
round(cast(SAL_AMT as REAL)/cast(all_amt as REAL)*100,1)||'%' as SAL_COMP,
round(cast(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc ) as REAL)/cast(all_amt as REAL)*100,1)||'%' as TTL_COMP,
case 
 when cast(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc ) as REAL)/cast(all_amt as REAL)*100 <= 40 then 'A'
 when cast(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc ) as REAL)/cast(all_amt as REAL)*100 <= 80 then 'B'
 ELSE 'C'
end as RANK
from target,all_amt
order by SAL_AMT desc,CODE desc