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'
)
    select
         ITEM_CODE CODE
        ,ITEM_NAME NAME
        ,SAL_AMT
        ,SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc) CML_AMT
        ,ROUND(SAL_AMT * 1.0 / A_PRICE * 100.0, 1) || '%' SAL_COMP
        ,ROUND(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc) * 1.0 / A_PRICE * 100.0, 1) || '%' TTL_COMP
        --,SUM(SAL_AMT) OVER (PARTITION BY SAL_AMT) SAL_SUM
        --,AVG(SAL_AMT) OVER (ORDER BY SAL_AMT desc) SAL_SUM2
        --,MIN(SAL_AMT) OVER (ORDER BY SAL_AMT desc) SAL_SUM3
        --,ROW_NUMBER() OVER (PARTITION BY SAL_AMT order by ITEM_CODE desc) ROWNUM
        --,A_PRICE
         ,case 
            when ROUND(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc) * 1.0/ A_PRICE * 100.0, 1)  <= 40 then 'A'
            when ROUND(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT desc) * 1.0/ A_PRICE * 100.0, 1)  between 41 and 80 then 'B'
            else 'C'
         end RANK
    from SUB1
         cross join SUB2
    ORDER BY SAL_AMT desc, ITEM_CODE desc