WITH Pre AS(
SELECT
    SD.ITEM_CODE AS CODE,
    I.ITEM_NAME  AS NAME,
    SUM(SD.UNITPRICE * SD.SALES_QTY) AS SAL_AMT
FROM
    SALES_DTL AS SD
INNER JOIN
    SALES AS S
ON
    SD.SALES_NO = S.SALES_NO
INNER JOIN
    ITEM AS I
ON
    SD.ITEM_CODE = I.ITEM_CODE
WHERE
    DATE(S.SALES_DATE) BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY
    SD.ITEM_CODE
ORDER BY
    SAL_AMT  desc,
    CODE     desc
),
Pre_ALL AS(
SELECT
    SUM(SD.UNITPRICE * SD.SALES_QTY) AS ALL_AMT
FROM
    SALES_DTL AS SD
INNER JOIN
    SALES AS S
ON
    SD.SALES_NO = S.SALES_NO
WHERE
    DATE(S.SALES_DATE) BETWEEN '2023-06-01' AND '2023-06-30'
),
Pre2 AS (
SELECT
    CODE,
    NAME,
    SAL_AMT,
    SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) AS CML_AMT,
    ROUND(
        100 * cast(SAL_AMT as real) / cast(ALL_AMT as real)
    ,1) AS SAL_COMP,
    ROUND(
    100 * CAST(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) as real) / cast(ALL_AMT as real)
    ,1) as TTL_COMP
FROM
    Pre
CROSS JOIN
	Pre_ALL
GROUP BY
    CODE
)
SELECT
    CODE,
    NAME,
    SAL_AMT,
    CML_AMT,
    SAL_COMP || '%' AS SAL_COMP,
    TTL_COMP || '%' AS TTL_COMP,
    CASE 
        WHEN TTL_COMP <= 40 THEN 'A'
        WHEN TTL_COMP > 40 AND TTL_COMP <= 80 THEN 'B'
        ELSE 'C'
    END AS RANK
FROM
    Pre2
ORDER BY
    SAL_COMP DESC,
    CODE DESC