WITH
    total_sales AS (
        SELECT
            SUM(UNITPRICE * SALES_QTY) AS total
        FROM
            SALES_DTL
            LEFT JOIN SALES ON SALES.SALES_NO = SALES_DTL.SALES_NO
        WHERE
            SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    )
  , sales_per_item AS (
        SELECT
            ITEM.ITEM_CODE
          , ITEM.ITEM_NAME
          , SUM(UNITPRICE * SALES_QTY) AS SAL_AMT
        FROM
            SALES_DTL
            LEFT JOIN SALES ON SALES.SALES_NO = SALES_DTL.SALES_NO
            INNER JOIN ITEM ON ITEM.ITEM_CODE = SALES_DTL.ITEM_CODE
        WHERE
            SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
        GROUP BY
            ITEM.ITEM_CODE
          , ITEM.ITEM_NAME
    )
  , sales_with_rownum AS (
        SELECT
            *
          , DENSE_RANK() OVER (
                ORDER BY
                    SAL_AMT DESC
            ) AS RN
        FROM
            sales_per_item
    )
  , sales_with_cml_amt AS (
        SELECT
            ITEM_CODE AS "CODE"
          , ITEM_NAME AS "NAME"
          , SAL_AMT
          , (
                SELECT
                    SUM(SAL_AMT)
                FROM
                    sales_with_rownum AS subquery
                WHERE
                    subquery.RN <= mainquery.RN
            ) AS CML_AMT
        FROM
            sales_with_rownum AS mainquery
    )
SELECT
    CODE
  , NAME
  , SAL_AMT
  , CML_AMT
  , CAST(ROUND((SAL_AMT * 1.0 / total) * 100, 1) AS TEXT) || '%' AS "SAL_COMP"
  , CAST(ROUND((CML_AMT * 1.0 / total) * 100, 1) AS TEXT) || '%' AS "TTL_COMP"
  , CASE
        WHEN 1.0 * CML_AMT / total <= 0.4 THEN 'A'
        WHEN 1.0 * CML_AMT / total <= 0.8 THEN 'B'
        ELSE 'C'
    END AS "RANK"
FROM
    sales_with_cml_amt
  , total_sales
ORDER BY
    SAL_AMT DESC
  , CODE DESC;