WITH ABC1 AS (
SELECT
SDTL.ITEM_CODE, I.ITEM_NAME
, SUM(SDTL.UNITPRICE * SDTL.SALES_QTY) AS SAL_AMT
FROM SALES AS SH
INNER JOIN SALES_DTL AS SDTL
ON SDTL.SALES_NO = SH.SALES_NO
INNER JOIN ITEM AS I
ON SDTL.ITEM_CODE = I.ITEM_CODE
WHERE SH.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY SDTL.ITEM_CODE
ORDER BY SAL_AMT DESC
)
, ABC2 AS (
SELECT
*
, ROUND(CAST(SAL_AMT AS REAL) * 100 / SUM(SAL_AMT) over(), 1) AS SAL_COMP
FROM ABC1
ORDER BY SAL_COMP DESC
)
, ABC3 AS (
SELECT
T1.*
, SUM(T2.SAL_AMT) AS CML_AMT
FROM
ABC2 AS T1
INNER JOIN ABC2 AS T2
ON T1.SAL_AMT <= T2.SAL_AMT
GROUP BY T1.ITEM_CODE, T1.SAL_AMT
)
, ABC4 AS (
SELECT
*
, ROUND(CAST(CML_AMT AS REAL) * 100 / SUM(SAL_AMT) over(), 1) AS TTL_COMP
FROM ABC3
)
SELECT
ITEM_CODE, ITEM_NAME, SAL_AMT, CML_AMT, SAL_COMP || '%' AS SAL_COMP, TTL_COMP || '%' AS TTL_COMP
, (CASE WHEN TTL_COMP <= 40 THEN "A"
        WHEN TTL_COMP <= 80 THEN "B"
        ELSE "C" END) AS"RANK"
FROM ABC4
ORDER BY SAL_AMT DESC, ITEM_CODE DESC;