with SUB1 as(
    select
        SD.ITEM_CODE, IT.ITEM_NAME, SUM(SD.UNITPRICE * SD.SALES_QTY) SAL_AMT
    from
        SALES SA
        inner join SALES_DTL SD
        on  SA.SALES_NO = SD.SALES_NO
        inner join ITEM IT
        on  SD.ITEM_CODE = IT.ITEM_CODE
    where SA.SALES_DATE between '2023-06-01' and '2023-06-30'
    group by SD.ITEM_CODE, IT.ITEM_NAME
)
,SUB2 as(
    select
         SUM(SD.UNITPRICE * SD.SALES_QTY) A_PRICE
    from
        SALES SA
        inner join SALES_DTL SD
        on  SA.SALES_NO = SD.SALES_NO
    where SA.SALES_DATE between '2023-06-01' and '2023-06-30'
)
,SUB3 as(
    select
         ITEM_CODE
        ,ITEM_NAME
        ,ROUND(SAL_AMT * 1.0 / A_PRICE * 100.0, 1) SAL_COMP
        ,SAL_AMT
        ,SUM(SAL_AMT) OVER (PARTITION BY SAL_AMT) SAL_SUM
        ,ROW_NUMBER() OVER (PARTITION BY SAL_AMT order by ITEM_CODE desc) ROWNUM
        ,A_PRICE
    from SUB1 cross join SUB2
)
,SUB4 as(
    select
         ITEM_CODE CODE
        ,ITEM_NAME NAME
        ,SAL_AMT
        ,A_PRICE
        ,SUM(SAL_SUM) OVER (order by SAL_AMT DESC, ITEM_CODE desc ROWS UNBOUNDED PRECEDING)
         - case when ROWNUM > 1 then SAL_SUM else 0 end CML_AMT
        ,SAL_COMP SAL_COMP
    from
        SUB3 
)
select CODE, NAME, SAL_AMT,
        CML_AMT, SAL_COMP || '%' SAL_COMP,
        ROUND(CML_AMT * 1.0/ A_PRICE * 100.0, 1)  || '%' TTL_COMP,
        case 
            when ROUND(CML_AMT * 1.0/ A_PRICE * 100.0, 1)  <= 40 then 'A'
            when ROUND(CML_AMT * 1.0/ A_PRICE * 100.0, 1)  between 41 and 80 then 'B'
            else 'C'
        end RANK
from  SUB4
order by SAL_AMT desc, CODE desc