with SA_TB AS
(
    SELECT
        ITEM_CODE,
        --RANK() OVER(ORDER BY SUM(UNITPRICE * SALES_QTY) DESC) SAL_RNK,
        SUM(UNITPRICE * SALES_QTY) SAL_AMT
        --SUM(UNITPRICE * SALES_QTY) OVER(PARTITION BY SALES_TYPE),
        --1.0*SUM(UNITPRICE * SALES_QTY)/
        --(SUM(UNITPRICE * SALES_QTY) OVER()) SAL_COMP
    FROM
        SALES SA
    JOIN
        SALES_DTL SAD
    ON  SA.SALES_NO = SAD.SALES_NO
    WHERE
        SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    group by ITEM_CODE
    --ORDER BY ROW_NUMBER() OVER(ORDER BY SUM(UNITPRICE * SALES_QTY) DESC)
),
SAL_COMP_TBL AS
(
    SELECT
        SA_TB.*,
        SUM(SAL_AMT) OVER(),
        1.0 * SAL_AMT/(SUM(SAL_AMT) OVER()) SAL_COMP
    FROM SA_TB
)
--select * from SAL_COMP_TBL

SELECT
    SCT1.ITEM_CODE CODE,
    I.ITEM_NAME NAME,
    SCT1.SAL_AMT,
    SUM(SCT2.SAL_AMT) CML_AMT,
    round(SCT1.SAL_COMP*100,1)||'%' SAL_COMP,
    round(SUM(SCT2.SAL_COMP)*100,1)||'%' TTL_COMP,
    case
    when 0.4 >= SUM(SCT2.SAL_COMP) then 'A'
    when 0.8 >= SUM(SCT2.SAL_COMP) then 'B'
    else 'C'
  end as RANK
FROM
    SAL_COMP_TBL SCT1 
JOIN  SAL_COMP_TBL SCT2 ON SCT1.SAL_AMT <= SCT2.SAL_AMT
LEFT JOIN ITEM I ON SCT1.ITEM_CODE = I.ITEM_CODE
group by
    SCT1.ITEM_CODE,
    SCT1.SAL_AMT,
    SCT1.SAL_COMP
ORDER BY
    SCT1.SAL_AMT DESC,
    SCT1.ITEM_CODE DESC