WITH SUB AS (
    SELECT I.ITEM_CODE AS CODE, I.ITEM_NAME AS NAME, SUM(UNITPRICE * SALES_QTY) AS SAL_AMT
    FROM ITEM I JOIN SALES_DTL SD ON I.ITEM_CODE = SD.ITEM_CODE JOIN SALES S ON SD.SALES_NO = S.SALES_NO
    WHERE S.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY I.ITEM_CODE
), A AS (
    SELECT SUM(SAL_AMT) AS SS
    FROM SUB
), B AS (
    SELECT S1.CODE AS CODE, S1.NAME AS NAME, S1.SAL_AMT AS SAL_AMT, SUM(S2.SAL_AMT) AS CML_AMT, CAST(S1.SAL_AMT AS REAL) / A.SS * 100 || '%' AS SAL_CMP
    FROM SUB S1 CROSS JOIN SUB S2 CROSS JOIN A
    WHERE (S1.SAL_AMT < S2.SAL_AMT) OR (S1.SAL_AMT = S2.SAL_AMT AND S1.CODE <= S2.CODE)
    GROUP BY S1.CODE
    ORDER BY SAL_AMT DESC, CODE DESC
)

SELECT B1.CODE AS CODE, B1.NAME AS NAME, B1.SAL_AMT AS SAL_AMT, B1.CML_AMT AS CML_AMT, ROUND(B1.SAL_CMP, 1) AS SAL_CMP,
SUM(B2.SAL_CMP) || '%' AS TTL_COMP,
CASE WHEN SUM(B2.SAL_CMP) <= 40 THEN 'A'
WHEN SUM(B2.SAL_CMP) <= 80 THEN 'B'
ELSE 'C' END AS RANK
FROM B B1 CROSS JOIN B B2
WHERE (B1.SAL_AMT < B2.SAL_AMT) OR (B1.SAL_AMT = B2.SAL_AMT AND B1.CODE <= B2.CODE)
GROUP BY B1.CODE
ORDER BY SAL_AMT DESC, CODE DESC;