WITH sales_summary AS (
    SELECT 
        ITEM.ITEM_CODE AS CODE,
        ITEM.ITEM_NAME AS NAME,
        SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS SAL_AMT
    FROM
        SALES
    JOIN
        SALES_DTL ON SALES.SALES_NO = SALES_DTL.SALES_NO
    JOIN
        ITEM ON SALES_DTL.ITEM_CODE = ITEM.ITEM_CODE
    WHERE
        SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY
        ITEM.ITEM_CODE, ITEM.ITEM_NAME
    ORDER BY
        SAL_AMT DESC
)
SELECT
    CODE,
    NAME,
    SAL_AMT,
    SUM(SAL_AMT) OVER (ORDER BY CODE DESC) AS CML_AMT,
    ROUND(SAL_AMT*100.0 / SUM(SAL_AMT) OVER (), 1) || '%' AS SAL_COMP,
    ROUND(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC)*100.0 / SUM(SAL_AMT) OVER (), 1) || '%' AS TTL_COMP,
    CASE
        WHEN ROUND(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC)*100.0 / SUM(SAL_AMT) OVER (), 1) <= 40 THEN 'A'
        WHEN ROUND(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC)*100.0 / SUM(SAL_AMT) OVER (), 1) <= 80 THEN 'B'
        ELSE 'C'
    END AS RANK
FROM
    sales_summary
ORDER BY
    SAL_AMT DESC,
    CODE DESC