with s as (select * from sales s join sales_dtl sd on s.sales_no = sd.sales_no where s.sales_date between '2023-06-01' and '2023-06-30'
)
, s2 as (select 
item_code
, sum(unitprice * sales_qty) as amt from s group by ITEM_CODE
)
, ts as (
select sum(amt) as ttl from s2
)
, s3 as (
select *, sum(amt) over(order by amt desc) as cml_amt from s2
)
, s4 as (select 
item_code
, s3.amt as SAL_AMT
, s3.cml_amt as CML_AMT
, ROUND(100.0 * s3.amt / ts.ttl, 1) as SAL_COMP
, ROUND(100.0 * s3.cml_amt / ts.ttl, 1) as TTL_COMP
from s3 cross join ts)
select 
s4.item_code as CODE
, item.item_name as NAME
, SAL_AMT
, CML_AMT
, SAL_COMP || '%' as SAL_COMP
, TTL_COMP || '%' as TTL_COMP
, case
when TTL_COMP <= 40 then 'A'
when TTL_COMP <= 80 then 'B'
else 'C'
end as RANK
from s4 join item on s4.item_code = item.item_code
order by SAL_AMT desc, CODE desc