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, A_PRICE
    from SUB1 cross join SUB2
)
    select
        A.ITEM_CODE CODE
        ,A.ITEM_NAME NAME
        ,A.SAL_AMT
        --,SUM(A.SAL_AMT) CML_AMT
        ,A.SAL_COMP SAL_COMP
        --,ROUND(SUM(A.SAL_AMT) * 1.0/ A.A_PRICE * 100.0, 1) TTL_COMP
        ,B.ITEM_CODE CODE
        ,B.ITEM_NAME NAME
        ,B.SAL_AMT
    from
        SUB3 A
        inner join SUB3 B
        on  A.SAL_COMP >= B.SAL_COMP
    --group by A.ITEM_CODE, A.SAL_COMP, A.SAL_AMT
    --order by A.SAL_COMP desc,A.ITEM_CODE  desc