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
        *
        , count(SAL_AMT) over(partition by SAL_AMT) as SAME_PRI
    from (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
        , coalesce(sum(SAL_AMT)
            over(order by SAL_AMT desc range between unbounded preceding and 1 preceding), 0)
            + SAL_AMT*SAME_PRI  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
;