WITH sales_summary AS (
  SELECT
    SALES_DTL.ITEM_CODE AS CODE,
    ITEM.ITEM_NAME AS NAME,
    SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS SAL_AMT
  FROM
    SALES
  JOIN
    SALES_DTL ON SALES.SALES_NO = SALES_DTL.SALES_NO
  JOIN
    ITEM ON SALES_DTL.ITEM_CODE = ITEM.ITEM_CODE
  WHERE
    SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
  GROUP BY
    SALES_DTL.ITEM_CODE, ITEM.ITEM_NAME
), sales_rank AS (
  SELECT
    CODE,
    NAME,
    SAL_AMT,
    (SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC)) AS CML_AMT,
    ROUND((1.0*SAL_AMT / SUM(SAL_AMT) OVER ()) * 100, 1) || '%' AS SAL_COMP,
    ROUND((1.0 *SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) / SUM(SAL_AMT) OVER ()) * 100, 1) || '%' AS TTL_COMP,
    CASE
      WHEN (1.0*SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) / SUM(SAL_AMT) OVER ()) <= 0.4 THEN 'A'
      WHEN (1.0* SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) / SUM(SAL_AMT) OVER ()) <= 0.8 THEN 'B'
      ELSE 'C'
    END AS RANK
  FROM
    sales_summary
)
SELECT
  CODE,
  NAME,
  SAL_AMT,
  CML_AMT,
  SAL_COMP,
  TTL_COMP,
  RANK
FROM
  sales_rank
ORDER BY
  SAL_AMT DESC, CODE DESC;