WITH ITEM_AMT AS (
	SELECT
		 B.ITEM_CODE CODE
		,SUM(B.UNITPRICE*B.SALES_QTY) SAL_AMT
	FROM
		 SALES A
		,SALES_DTL B
	WHERE A.SALES_NO = B.SALES_NO 
	AND SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
	GROUP BY B.ITEM_CODE
	ORDER BY 2 DESC
),
SUM_AMT AS (
	SELECT
		SUM(B.UNITPRICE*B.SALES_QTY) SAL_AMT
	FROM
		 SALES A
		,SALES_DTL B
	WHERE A.SALES_NO = B.SALES_NO 
	AND SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
),
URIAGE_HI AS (
	SELECT 
		"TEST" TEST
		,A.CODE
		,A.SAL_AMT SAL_AMT
		,B.SAL_AMT CML_AMT
		, ROUND((A.SAL_AMT*1.00/B.SAL_AMT*1.00),2) SAL_COMP
	FROM ITEM_AMT A
		,SUM_AMT B
	ORDER BY 5 DESC
)
SELECT CODE
	,SAL_AMT
	,SUM(SAL_AMT) OVER (PARTITION BY TEST ORDER BY SAL_COMP) CML_AMT
	,SAL_COMP
	,SUM(SAL_COMP) OVER (PARTITION BY TEST ORDER BY SAL_COMP) TTL_COMP
FROM URIAGE_HI
ORDER BY 2 DESC, 1 DESC