WITH T as (
  SELECT
    D.ITEM_CODE
   ,SUM(D.UNITPRICE * D.SALES_QTY) SAL_AMT
  FROM SALES S
  JOIN SALES_DTL D ON S.SALES_NO = D.SALES_NO
  WHERE S.SALES_DATE >= '2023-06-01' and S.SALES_DATE <= '2023-06-30'
  GROUP BY D.ITEM_CODE
), A AS (
SELECT 
 I.ITEM_CODE as CODE
,I.ITEM_NAME as NAME
,T.SAL_AMT
,rank() over (order by T.SAL_AMT desc) rank
FROM ITEM I
JOIN T ON I.ITEM_CODE = T.ITEM_CODE
), R AS (
SELECT
  A1.CODE
 ,A1.NAME
 ,A1.SAL_AMT
 ,A1.rank
 ,(SELECT SUM(A2.SAL_AMT) FROM A A2 WHERE A2.rank <= A1.rank) CML_AMT
 ,ROUND(100.0 * A1.SAL_AMT / TT.TOTAL_AMT, 1) || '%' SAL_COMP
 ,100.0 * (SELECT SUM(A2.SAL_AMT) FROM A A2 WHERE A2.rank <= A1.rank) / TT.TOTAL_AMT TTL_COMP
FROM A A1, (SELECT SUM(SAL_AMT) TOTAL_AMT FROM A) TT
)
SELECT
CODE,NAME,SAL_AMT,CML_AMT, SAL_COMP, ROUND(TTL_COMP, 1) || '%' TTL_COMP,
CASE 
  WHEN TTL_COMP <= 40 THEN 'A'
  WHEN TTL_COMP <= 80 THEN 'B'
  ELSE 'C'
END RANK
FROM R
ORDER BY rank, CODE desc