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