with tmp as(
select
    sa.item_code as CODE
    ,it.item_name as NAME
    ,sum(sa.unitprice * sa.SALES_QTY) as SAL_AMT
from sales_dtl as sa
inner join sales as sal
on sa.sales_no = sal.sales_no
inner join item as it
on sa.item_code = it.item_code
    
where
    sal.sales_date between date('2023-06-01') and date('2023-06-30')
group by
    CODE,NAME
)
select
    CODE
    ,NAME
    ,SAL_AMT
    ,sum(SAL_AMT) over (order by SAL_AMT desc) AS CML_AMT
    ,round(cast(SAL_AMT as double) / (select sum(sal_amt) from tmp) *100,1) || '%' as SAL_COMP
    ,round(cast(sum(SAL_AMT) over (order by SAL_AMT desc) as REAL) / (select sum(sal_amt) from tmp) *100,1) || '%' as TTL_COMP
    ,CASE
        WHEN round(cast(sum(SAL_AMT) over (order by SAL_AMT desc) as REAL) / (select sum(sal_amt) from tmp) *100,1) <= 40 THEN 'A'
        WHEN round(cast(sum(SAL_AMT) over (order by SAL_AMT desc) as REAL) / (select sum(sal_amt) from tmp) *100,1) <= 80 THEN 'B'
        ELSE 'C'
        END AS RANK
from tmp
order by 
    SAL_AMT desc
    ,CODE desc