WITH TMP AS (
SELECT
    RANK() OVER(ORDER BY SUM(SD.SALES_QTY * SD.UNITPRICE) DESC) r
    , SD.ITEM_CODE AS CODE
    , I.ITEM_NAME AS NAME
    , SUM(SD.SALES_QTY * SD.UNITPRICE) AS SAL_AMT
FROM SALES_DTL SD
INNER JOIN SALES D
ON SD.SALES_NO = D.SALES_NO
AND '2023-06-01' <= D.SALES_DATE AND D.SALES_DATE <= '2023-06-30'
LEFT JOIN ITEM I
ON SD.ITEM_CODE = I.ITEM_CODE
GROUP BY SD.ITEM_CODE, I.ITEM_NAME
ORDER BY SAL_AMT DESC, CODE DESC
)
, TMP2 AS (
SELECT
    CODE
    , NAME
    , SAL_AMT
    , (SELECT SUM(T.SAL_AMT) FROM TMP AS T WHERE T.r <= TMP.r) AS CML_AMT
    , ROUND((CAST(SAL_AMT AS REAL) / CAST((SELECT SUM(SAL_AMT) FROM TMP) AS REAL)) * 100, 1) AS SAL_COMP
    , ROUND((CAST(((SELECT SUM(T.SAL_AMT) FROM TMP AS T WHERE T.r <= TMP.r)) AS REAL) / CAST((SELECT SUM(SAL_AMT) FROM TMP) AS REAL)) * 100, 1) AS TTL_COMP
    , (SELECT SUM(T2.SAL_AMT) FROM TMP T2) AS ALL_AMT
FROM TMP
)
SELECT
    CODE
    , NAME
    , SAL_AMT
    , CML_AMT
    , CAST(SAL_COMP AS TEXT) || '%' AS SAL_COMP
    , CAST(TTL_COMP AS TEXT) || '%' AS TTL_COMP
    , CASE
          WHEN TMP2.TTL_COMP <= 40 THEN 'A'
          WHEN TMP2.TTL_COMP <= 80 THEN 'B'
          ELSE 'C'
      END AS RANK
FROM TMP2
ORDER BY SAL_AMT DESC, CODE DESC;