with sales_base as (
    select
        sd.ITEM_CODE
        ,sum(UNITPRICE*SALES_QTY) as SAL_AMT
    from
        SALES_DTL as sd
        inner join
        SALES as s
        on sd.SALES_NO = s.SALES_NO
    where
        SALES_DATE between "2023-06-01" and "2023-06-30"
    group by
        sd.ITEM_CODE
)
,base1 as (
select
    ITEM_CODE
    ,SAL_AMT
    ,100.0*SAL_AMT/sum(SAL_AMT) over() as SAL_COMP
from
    sales_base
)
,base2 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
    ,SAL_COMP
    ,sum(SAL_COMP) over (order by SAL_COMP desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as TTL_COMP
from
    base1
)
select
    b.ITEM_CODE
    ,i.ITEM_NAME
    ,SAL_AMT
    ,round(SAL_COMP,1)||"%" as SAL_COMP
    ,round(TTL_COMP,1)||"%" as TTL_COMP
    ,case
        when TTL_COMP<=40 then "A"
        when TTL_COMP<=80 then "B"
        else "C"
    end as RANK
from
    base2 as b
    inner join
    ITEM as i
    on b.ITEM_CODE = i.ITEM_CODE
order by
    SAL_AMT desc
    ,b.ITEM_CODE