with TOTAL as (
    select
    	sum(SD.UNITPRICE*SD.SALES_QTY) as SAL_TOTAL
    from SALES as S
    inner join SALES_DTL as SD
    	on S.SALES_NO=SD.SALES_NO
    where S.SALES_DATE like '2023-06%'
), SALES_AMT as (
    select
    	SD.ITEM_CODE
    	, sum(SD.UNITPRICE*SD.SALES_QTY) as SAL_AMT
    from SALES as S
    inner join SALES_DTL as SD
    	on S.SALES_NO=SD.SALES_NO
    where S.SALES_DATE like '2023-06%'
    group by SD.ITEM_CODE
), SALES_CML_AMT as (
    select
        ITEM_CODE
        , SAL_AMT
        , sum(SAL_AMT) over(order by SAL_AMT desc rows between unbounded preceding and current row) as CML_AMT
    from SALES_AMT
)
select
    CODE
    , NAME
    , SAL_AMT
    , CML_AMT
    , SAL_COMP || '%' as SAL_COMP
    , CML_COMP || '%' as CML_COMP
    , case
        when CML_COMP<=40.0 then 'A'
        when CML_COMP<=80.0 then 'B'
        else 'C'
    end as RANK
from (select
        SCM.ITEM_CODE as CODE
        , ITEM_NAME as NAME
        , SAL_AMT
        , CML_AMT
        , round(cast(SAL_AMT as real) / T.SAL_TOTAL * 100, 1) as SAL_COMP
        , round(cast(CML_AMT as real) / T.SAL_TOTAL * 100, 1) as CML_COMP
    from SALES_CML_AMT as SCM, TOTAL as T
    inner join ITEM as I on SCM.ITEM_CODE=I.ITEM_CODE
    )
order by SAL_AMT desc, CODE desc
;