WITH STEP1 
AS( 
SELECT
    S_TDL.ITEM_CODE AS CODE
    ,SUM(S_TDL.UNITPRICE * S_TDL.SALES_QTY ) AS SAL_AMT
FROM
    SALES_DTL AS S_TDL
JOIN
    SALES AS S
ON
    S_TDL.SALES_NO = S.SALES_NO
WHERE
    S.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY
    S_TDL.ITEM_CODE
)

,STEP2 
AS(
SELECT 
    S1.CODE
    ,I.ITEM_NAME AS NAME
    ,S1.SAL_AMT
FROM
    STEP1 AS S1
JOIN
    ITEM AS I
ON
    S1.CODE = I.ITEM_CODE
)

,STEP3
AS(
SELECT 
    CODE
    ,NAME
    ,SAL_AMT
    ,SUM(SAL_AMT) OVER(ORDER BY SAL_AMT DESC ) AS CML_AMT
FROM
    STEP2
GROUP BY
    CODE
)

,STEP4 
AS(
SELECT
    CODE
    ,NAME
    ,SAL_AMT
    ,CML_AMT
    ,ROUND(100*(1.0*SAL_AMT / (SELECT SUM(SAL_AMT) FROM STEP2 )),2) AS SAL_COMP
FROM
    STEP3
    
)
,STEP5 
AS(
SELECT
    CODE
    ,NAME
    ,SAL_AMT
    ,CML_AMT
    ,SAL_COMP
    ,SUM(SAL_COMP) OVER (ORDER BY SAL_COMP DESC) AS TTL_COMP
FROM
    STEP4
GROUP BY CODE
)

SELECT 
    CODE
    ,NAME
    ,SAL_AMT
    ,CML_AMT
    ,FORMAT(ROUND(SAL_COMP,1),1) || '%' AS SAL_COMP
    ,FORMAT(ROUND(TTL_COMP,1),1) || '%' AS TTL_COMP
    ,CASE 
        WHEN TTL_COMP <= 40.0 THEN 'A'
        WHEN TTL_COMP <= 80.0 THEN 'B'
        ELSE 'C'
    END AS RANK
FROM
    STEP5
ORDER BY
    SAL_AMT DESC
    ,CODE DESC;