WITH STEP1 AS ( 
  SELECT
      ITEM_CODE
    , SUM(UNITPRICE * SALES_QTY) PRICE
    , RANK() OVER (ORDER BY SUM(UNITPRICE * SALES_QTY) DESC) S1RANK 
  FROM
    SALES S 
    INNER JOIN SALES_DTL SD 
      ON S.SALES_NO = SD.SALES_NO 
  WHERE
    S.SALES_DATE >= DATE ('2023-06-01') 
    AND S.SALES_DATE <= DATE ('2023-06-30') 
  GROUP BY
    ITEM_CODE
) 
, TOTAL AS ( 
  SELECT
      SUM(UNITPRICE * SALES_QTY) TOTAL 
  FROM
    SALES S 
    INNER JOIN SALES_DTL SD 
      ON S.SALES_NO = SD.SALES_NO 
  WHERE
    S.SALES_DATE >= DATE ('2023-06-01') 
    AND S.SALES_DATE <= DATE ('2023-06-30')
) 
, SETP2 AS ( 
  SELECT
      ITEM_CODE
    , PRICE
    , S1RANK
    , TOTAL
    , (PRICE * 1.0) / TOTAL AS PER
    , RANK() OVER (ORDER BY (PRICE * 1.0) / TOTAL DESC) S2RANK 
  FROM
    STEP1
    , TOTAL
) 
, STEP3 AS ( 
  SELECT
      ITEM_CODE
    , PRICE
    , SUM(PRICE) OVER(ORDER BY S2RANK) SUMPRICE
    , ROUND(PER * 100, 1) SAL_COMP
    , ROUND((SUM(PER) OVER(ORDER BY S2RANK)) * 100, 1) TTL_COMP 
  FROM
    SETP2 S2
) 
SELECT
    STEP3.ITEM_CODE       AS CODE
  , ITEM.ITEM_NAME        AS NAME
  , STEP3.PRICE           AS SAL_AMT
  , STEP3.SUMPRICE        AS CML_AMT
  , STEP3.SAL_COMP || '%' AS SAL_COMP
  , STEP3.TTL_COMP || '%' AS TTL_COMP
  , CASE 
    WHEN STEP3.TTL_COMP <= 40 
      THEN 'A' 
    WHEN STEP3.TTL_COMP <= 80 
      THEN 'B' 
    ELSE 'C' 
    END                   AS RANK 
FROM
  STEP3 
  INNER JOIN ITEM 
    ON STEP3.ITEM_CODE = ITEM.ITEM_CODE 
ORDER BY
  STEP3.PRICE DESC
  , STEP3.ITEM_CODE DESC