with 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 range between unbounded preceding and current row) as CML_AMT
    from SALES_AMT
), TOTAL as (
    select
    	sum(SAL_AMT) as SAL_TOTAL
    from SALES_AMT
), SALES_SUMMARY as (
    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_NUM
        , round(cast(CML_AMT as real) / T.SAL_TOTAL * 100, 1) as TTL_COMP_NUM
    from SALES_CML_AMT as SCM, TOTAL as T
    inner join ITEM as I on SCM.ITEM_CODE=I.ITEM_CODE
)

select
    CODE
    , NAME
    , SAL_AMT
    , CML_AMT
    , SAL_COMP_NUM || '%' as SAL_COMP
    , TTL_COMP_NUM || '%' as TTL_COMP
    , case
        when TTL_COMP_NUM<=40.0 then 'A'
        when TTL_COMP_NUM<=80.0 then 'B'
        else 'C'
    end as RANK
from SALES_SUMMARY
order by SAL_AMT desc, CODE desc
;