with SAL_AMT as(
    select
        ITEM.ITEM_CODE,
        ITEM.ITEM_NAME,
        sum(UNITPRICE * SALES_QTY) as SAL_AMT
    from
        SALES
        inner join SALES_DTL on SALES.SALES_NO = SALES_DTL.SALES_NO
        inner join ITEM on SALES_DTL.ITEM_CODE = ITEM.ITEM_CODE
    where
        SALES_DATE between '2023-06-01'
        and '2023-06-30'
    group by
        ITEM.ITEM_CODE
    order by
        SAL_AMT desc
),
SAL_COMP as(
    select
        rank() over(
            order by
                SAL_AMT desc
        ) as RANK,
        ITEM_CODE,
        ITEM_NAME,
        SAL_AMT,
        sum(SAL_AMT) over() as TOTAL_SAL_AMT,
        (cast(SAL_AMT as real) / cast(sum(SAL_AMT) over() as real)) * 100 as SAL_COMP
    from
        SAL_AMT
    order by
        SAL_COMP desc,
        ITEM_CODE desc
)
select
    A.ITEM_CODE as CODE,
    A.ITEM_NAME as NAME,
    A.SAL_AMT,
    sum(B.SAL_AMT) as CML_AMT,
    round(A.SAL_COMP, 1) || '%' as SAL_COMP,
    round(sum(B.SAL_COMP), 1) || '%' as TTL_COMP,
    case
        when sum(B.SAL_COMP) <= 40 then 'A'
        when sum(B.SAL_COMP) <= 80 then 'B'
        else 'C'
    end as RANK
from
    SAL_COMP A
    inner join SAL_COMP as B on A.RANK >= B.RANK
group by
    A.RANK,
    A.ITEM_CODE,
    A.ITEM_NAME,
    A.TOTAL_SAL_AMT
order by
    A.SAL_COMP desc,
    A.ITEM_CODE desc