with DATA AS (
    select 
    ITEM_CODE
    ,CAST(SUM(UNITPRICE*DELIVERED_QTY) AS FLOAT)/
        (
            select CAST(SUM(UNITPRICE*DELIVERED_QTY) AS FLOAT) AS BUNBO
            from SALES_DTL a
            INNER JOIN 
            SALES b
            ON
            a.SALES_NO = b.SALES_NO
            WHERE 
            SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
        ) AS SAL_COMP
    ,SUM(UNITPRICE*DELIVERED_QTY) as SAL_AMT
    from SALES_DTL a
    INNER JOIN 
    SALES b
    ON
    a.SALES_NO = b.SALES_NO
    WHERE 
    SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY 
    ITEM_CODE
    ORDER BY 
    SUM(UNITPRICE*DELIVERED_QTY) desc
)

SELECT 
CODE
,NAME
,SAL_AMT
,CML_AMT
,CAST(SAL_COMP AS NCHAR(5))||'%' AS SAL_COMP
,CAST(TTL_COMP AS NCHAR(5))||'%' 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
(
    SELECT
    a.ITEM_CODE AS CODE
    ,b.ITEM_NAME AS NAME
    ,SAL_AMT
    ,sum(SAL_AMT) over (order by SAL_AMT desc rows between unbounded preceding and current row) AS CML_AMT
    ,ROUND(SAL_COMP*100,1) as SAL_COMP
    ,ROUND(sum(SAL_COMP) over (order by SAL_AMT desc rows between unbounded preceding and current row)*100,1) AS TTL_COMP
    FROM DATA a
    INNER JOIN
    ITEM b
    ON
    a.ITEM_CODE = b.ITEM_CODE
)c