WITH 
TOTAL_SALES AS (
    SELECT SUM(SD.UNITPRICE * SD.SALES_QTY) AS TOTAL_SALES_AMT
    FROM SALES S 
    INNER JOIN SALES_DTL SD ON S.SALES_NO = SD.SALES_NO
    WHERE S.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
),
SALES_PER_ITEM AS (
    SELECT 
        I.ITEM_CODE AS CODE,
        I.ITEM_NAME AS NAME,
        SUM(SD.UNITPRICE * SD.SALES_QTY) AS SAL_AMT,
        ROUND(SUM(SD.UNITPRICE * SD.SALES_QTY) / TS.TOTAL_SALES_AMT * 100, 2) AS SAL_COMP
    FROM ITEM I
    INNER JOIN SALES_DTL SD ON I.ITEM_CODE = SD.ITEM_CODE
    INNER JOIN SALES S ON S.SALES_NO = SD.SALES_NO
    CROSS JOIN TOTAL_SALES TS
    WHERE S.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY I.ITEM_CODE, I.ITEM_NAME
    ORDER BY SAL_AMT DESC, CODE DESC
),
CUMULATIVE_SALES AS (
    SELECT 
        CODE,
        NAME,
        SAL_AMT,
        SAL_COMP || '%' AS SAL_COMP,
        ROUND(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC, CODE DESC) / TS.TOTAL_SALES_AMT * 100, 2) AS CML_AMT,
        ROUND(SUM(SAL_COMP) OVER (ORDER BY SAL_AMT DESC, CODE DESC), 2) || '%' AS TTL_COMP
    FROM SALES_PER_ITEM
    CROSS JOIN TOTAL_SALES TS
)
SELECT 
    CODE,
    NAME,
    SAL_AMT,
    CML_AMT || '%' AS CML_AMT,
    SAL_COMP,
    TTL_COMP,
    CASE 
        WHEN CAST(REPLACE(TTL_COMP, '%', '') AS FLOAT) <= 40 THEN 'A'
        WHEN CAST(REPLACE(TTL_COMP, '%', '') AS FLOAT) <= 80 THEN 'B'
        ELSE 'C'
    END AS RANK
FROM CUMULATIVE_SALES
ORDER BY SAL_AMT DESC, CODE DESC;