WITH tmp AS (
  SELECT d.ITEM_CODE CODE, i.ITEM_NAME NAME, SUM(d.UNITPRICE * d.SALES_QTY) SAL_AMT
  FROM SALES s, SALES_DTL d, ITEM i
  WHERE s.SALES_NO = d.SALES_NO
    AND d.ITEM_CODE = i.ITEM_CODE
    AND SALES_DATE >= '2023-06-01'
    AND SALES_DATE <= '2023-06-30'
  GROUP BY d.ITEM_CODE
  ORDER BY SAL_AMT DESC
),
total_amt AS (SELECT SUM(SAL_AMT) FROM tmp),
tmp1 AS (
  SELECT 
    CODE,
    NAME,
    SAL_AMT,
    SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) CML_AMT,
    100.0 * (SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS)) / (SELECT * FROM total_amt) RAW_TTL_COMP
  FROM tmp
)
SELECT
  CODE,
  NAME,
  SAL_AMT,
  CML_AMT,
  FORMAT('%.01f%%', ROUND(100.0 * SAL_AMT / (SELECT * FROM total_amt), 1)) SAL_COMP,
  FORMAT('%.01f%%', ROUND(RAW_TTL_COMP, 1)) TTL_COMP,
  IIF(RAW_TTL_COMP <= 40.0, 'A', IIF(RAW_TTL_COMP <= 80.0, 'B', 'C')) RANK
FROM tmp1
ORDER BY SAL_AMT DESC, CODE DESC