with TMP as (
select
    I.ITEM_CODE as code
    ,item_name as name
    ,sum(unitprice * sales_qty) as sal_amt
from SALES S 
    inner join SALES_DTL SD
        on S.sales_no =SD.sales_no
    inner join ITEM I
        on SD.item_code = I.item_code
where
    sales_date between '2023-06-01' and '2023-06-30'
group by 1,2
)
,TMP2 as (
select 
    *
    , rank() over (partition by '' order by sal_amt desc) as RN 
    ,sum(sal_amt) over (partition by '') as total_sal
from TMP
order by 3 desc
)
,TMP3 as (
select
    T.CODE, T.NAME ,T.SAL_AMT
    ,1.0*T.sal_amt / T.total_sal as SAL_COMP
    ,sum(TT.sal_amt) as CML_AMT
    ,1.0*sum(TT.sal_amt)/T.total_sal as TTL_COMP
from
    TMP2 T
    left outer join
        TMP2 TT
        on 
            T.RN >= TT.RN
group by 1,2,3,4
)
select
    CODE
    ,NAME
    ,SAL_AMT
    ,CML_AMT
    ,case 
        when SAL_COMP=1 then '100%'
        when SAL_COMP>=0.1 then substr(round(SAL_COMP*100, 1) || '.0',1,4) || '%'  --
        else substr(round(SAL_COMP*100, 1) || '.0',1,3) || '%'  --
        end as SAL_COMP
    ,case 
        when TTL_COMP=1 then '100%'
        when TTL_COMP>=0.1 then substr(round(TTL_COMP*100, 1) || '.0',1,4) || '%'  --
        else substr(round(TTL_COMP*100, 1) || '.0',1,3) || '%'  --
        end as TTL_COMP
    ,case
        when TTL_COMP<=0.4 then 'A'
        when TTL_COMP<=0.8 then 'B'
        else 'C'
        end as RANK
from
    TMP3
order by SAL_AMT desc, CODE
;