SELECT
    CODE AS CODE
    ,NAME AS NAME
    ,SAL_AMT AS SAL_AMT
    ,max(CML_AMT) OVER (PARTITION BY SAL_AMT) AS CML_AMT
    ,ROUND(SAL_COMP,1) || '%' AS SAL_COMP
    ,ROUND(max(TTL_COMP) OVER (PARTITION BY SAL_AMT),1) || '%' AS TTL_COMP
    ,RANK
FROM
(
    SELECT
    	 ITEM_CODE AS CODE
    	,ITEM_NAME AS NAME
    	,TotalPerITEM AS SAL_AMT
    	,SUM(TotalPerITEM) OVER (ORDER BY 1 ROWS 9999 PRECEDING) AS CML_AMT
    	,CAST(TotalPerITEM AS REAL) / Total * 100 AS SAL_COMP
    	,CAST(SUM(TotalPerITEM) OVER (ORDER BY 1 ROWS 9999 PRECEDING) AS REAL) / Total * 100 AS TTL_COMP
    	,CASE
    		WHEN (CAST(SUM(TotalPerITEM) OVER (ORDER BY 1 ROWS 9999 PRECEDING) AS REAL) / Total * 100) <= 40 THEN 'A'
    		WHEN (CAST(SUM(TotalPerITEM) OVER (ORDER BY 1 ROWS 9999 PRECEDING) AS REAL) / Total * 100) <= 80 THEN 'B'
    		ELSE 'C'
    	END AS RANK
    FROM
    (
    	SELECT
    		DISTINCT
    			t2.ITEM_CODE,
    			t3.ITEM_NAME,
    			SUM(UNITPRICE * SALES_QTY) OVER (PARTITION BY t2.ITEM_CODE) AS TotalPerITEM,
    			SUM(UNITPRICE * SALES_QTY) OVER () AS Total
    	FROM
    		SALES t1
    	INNER JOIN SALES_DTL t2 ON t1.SALES_NO  = t2.SALES_NO
    	INNER JOIN ITEM      t3 ON t2.ITEM_CODE = t3.ITEM_CODE
    	WHERE
    		SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    	ORDER BY
    		TotalPerITEM DESC
    )T
)
ORDER BY SAL_AMT DESC,CODE DESC