WITH 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
    INNER JOIN
        SALES_DTL ON
        SALES_DTL.SALES_NO = SALES.SALES_NO
    INNER JOIN
        ITEM ON
        ITEM.ITEM_CODE = SALES_DTL.ITEM_CODE
    WHERE
        SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY
        ITEM.ITEM_CODE
    ORDER BY
        SAL_AMT DESC
)
SELECT
    CODE,
    NAME,
    SAL_AMT,
    CML_AMT,
    PRINTF('%4.1f%%', ROUND(SAL_COMP, 1)) AS SAL_COMP,
    PRINTF('%4.1f%%', ROUND(TTL_COMP, 1)) AS TTL_COMP,
    CASE 
    WHEN ROUND(TTL_COMP, 1) <= 40 THEN 'A'
    WHEN ROUND(TTL_COMP, 1) <= 80 THEN 'B'
    ELSE 'C'
    END AS RANK
FROM (
    SELECT
        *,
        (
            SELECT
                SUM(ACCUMULATION.SAL_AMT)
            FROM
                SUMMARY AS
                ACCUMULATION
            WHERE
                ACCUMULATION.SAL_AMT >= SUMMARY.SAL_AMT
        ) AS CML_AMT,
        CAST(SAL_AMT * 100 AS REAL) / (SELECT SUM(SAL_AMT) FROM SUMMARY) AS SAL_COMP,
        (
            SELECT
                CAST(SUM(ACCUMULATION.SAL_AMT) * 100 AS REAL) / (SELECT SUM(SAL_AMT) FROM SUMMARY)
            FROM
                SUMMARY AS
                ACCUMULATION
            WHERE
                ACCUMULATION.SAL_AMT >= SUMMARY.SAL_AMT
        ) AS TTL_COMP
    FROM
        SUMMARY
) AS SUMMARY_TBL
ORDER BY
    SAL_AMT DESC,
    CODE DESC