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
   ,TT.TOTAL_AMT
  FROM ITEM I, (SELECT SUM(SAL_AMT) TOTAL_AMT FROM T) TT
  JOIN T ON I.ITEM_CODE = T.ITEM_CODE
), R AS (
  SELECT
    A1.CODE
   ,A1.NAME
   ,A1.SAL_AMT
   ,(SELECT SUM(A2.SAL_AMT) FROM A A2 WHERE A2.rank <= A1.rank) CML_AMT
   ,A1.rank
   ,A1.TOTAL_AMT
  FROM A A1
), R2 AS (
  SELECT
    CODE
   ,NAME
   ,SAL_AMT
   ,CML_AMT
   ,rank
   ,100.0 * SAL_AMT / TOTAL_AMT SAL_COMP
   ,100.0 * CML_AMT / TOTAL_AMT TTL_COMP
  FROM R
)
SELECT
CODE,NAME,SAL_AMT,CML_AMT,
ROUND(SAL_COMP, 1) || '%' 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 R2
ORDER BY rank, CODE desc