WITH SUB1 AS (
    SELECT
        ITEM_CODE
        ,SUM(UNITPRICE * SALES_QTY) AS SAL_AMT
    FROM
        SALES
            INNER JOIN SALES_DTL
                ON SALES.SALES_NO = SALES_DTL.SALES_NO
    WHERE
        DATE(SALES.SALES_DATE) BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY
        SALES_DTL.ITEM_CODE
    ORDER BY
        SAL_AMT DESC
), SUB2 AS(
    SELECT
        ITEM_CODE
        ,SAL_AMT
        ,ROUND(100.0 * SAL_AMT / SUM(SAL_AMT) OVER(), 1) AS SAL_COMP
        ,SUM(SAL_AMT) OVER(ORDER BY SAL_AMT DESC) AS CML_AMT
    FROM
        SUB1
), SUB3 AS(
    SELECT
        *
        ,ROUND(100.0 * CML_AMT / MAX(CML_AMT) OVER(), 1) AS TTL_COMP
    FROM
        SUB2
)
SELECT
    SUB3.ITEM_CODE AS CODE
    ,ITEM.ITEM_NAME AS NAME
    ,SUB3.SAL_AMT
    ,SUB3.CML_AMT
    ,SUB3.SAL_COMP || '%' AS SAL_COMP
    ,SUB3.TTL_COMP || '%' AS TTL_COMP
    ,CASE
        WHEN TTL_COMP <= 40 THEN 'A'
        WHEN TTL_COMP <= 80 THEN 'B'
        ELSE 'C'
        END AS RANK
FROM
    SUB3
        INNER JOIN ITEM
            ON SUB3.ITEM_CODE = ITEM.ITEM_CODE