WITH ITEM_AMT AS (
	SELECT
		 B.ITEM_CODE CODE
		,C.ITEM_NAME NAME
		,SUM(B.UNITPRICE*B.SALES_QTY) SAL_AMT
	FROM
		 SALES A
		,SALES_DTL B
		,ITEM C
	WHERE A.SALES_NO = B.SALES_NO 
	AND SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
	AND B.ITEM_CODE = C.ITEM_CODE
	GROUP BY B.ITEM_CODE,C.ITEM_NAME
),
RANK AS (
	SELECT
		 CODE
		,NAME
		,SAL_AMT
		,(SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC)) CML_AMT
		,ROUND((1.0 * SAL_AMT / SUM(SAL_AMT) OVER ()) * 100, 1) || '%' SAL_COMP
		,ROUND((1.0 * SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) / SUM(SAL_AMT) OVER ()) * 100, 1) || '%' TTL_COMP
		,CASE
			WHEN (1.0 * SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) / SUM(SAL_AMT) OVER ()) <= 0.4 THEN 'A'
			WHEN (1.0 * SUM(SAL_AMT) OVER (ORDER BY SAL_AMT DESC) / SUM(SAL_AMT) OVER ()) <= 0.8 THEN 'B'
			ELSE 'C'
		END RANK
	FROM
		 ITEM_AMT
)
SELECT CODE
	,CODE
	,NAME
	,SAL_AMT
	,CML_AMT
	,SAL_COMP
	,TTL_COMP
	,RANK
FROM RANK
ORDER BY 4 DESC, 1 DESC