SELECT
	[SAL_SUM].[CODE]
	,[SAL_SUM].[NAME]
	,[SAL_SUM].[SAL_AMT]
	,[SAL_SUM].[SAL_AMT] + SUM(IFNULL([SAL_SUM2].[SAL_AMT], 0)) AS [CML_AMT]
    ,ROUND(CAST([SAL_SUM].[SAL_AMT] AS REAL) / CAST([SAL_SUM].[ALL_SAL_AMT] AS REAL) * 100, 1) || '%' AS SAL_COMP
    ,ROUND(CAST([SAL_SUM].[SAL_AMT] + SUM(IFNULL([SAL_SUM2].[SAL_AMT], 0)) AS REAL) / CAST([SAL_SUM].[ALL_SAL_AMT] AS REAL) * 100, 1) || '%' AS TTL_COMP
    ,CASE
        WHEN ROUND(CAST([SAL_SUM].[SAL_AMT] + SUM(IFNULL([SAL_SUM2].[SAL_AMT], 0)) AS REAL) / CAST([SAL_SUM].[ALL_SAL_AMT] AS REAL) * 100, 1) <= 40
        THEN 'A'
        WHEN ROUND(CAST([SAL_SUM].[SAL_AMT] + SUM(IFNULL([SAL_SUM2].[SAL_AMT], 0)) AS REAL) / CAST([SAL_SUM].[ALL_SAL_AMT] AS REAL) * 100, 1) <= 80
        THEN 'B'
        ELSE 'C'
    END AS RANK
FROM (
	SELECT *
		,RANK() OVER(ORDER BY [SAL_AMT] DESC) AS RANKN
	FROM (
		SELECT
			ITEM.ITEM_CODE AS [CODE]
			,ITEM.ITEM_NAME AS [NAME]
			,SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS [SAL_AMT]
			,[ALL_SUM].[ALL_SAL_AMT]
		FROM SALES
		INNER JOIN SALES_DTL
			ON SALES_DTL.SALES_NO = SALES.SALES_NO
		INNER JOIN ITEM
			ON ITEM.ITEM_CODE = SALES_DTL.ITEM_CODE
		INNER JOIN (
			SELECT
				SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS [ALL_SAL_AMT]
			FROM SALES
			INNER JOIN SALES_DTL
				ON SALES_DTL.SALES_NO = SALES.SALES_NO
			WHERE SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
		) AS [ALL_SUM]
			ON 1 = 1
		WHERE SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
		GROUP BY
			ITEM.ITEM_CODE
			,ITEM.ITEM_NAME
			,[ALL_SUM].[ALL_SAL_AMT]
		) AS [SAL_SUM]
) AS [SAL_SUM]
LEFT OUTER JOIN (
	SELECT *
		,RANK() OVER(ORDER BY [SAL_AMT] DESC) AS RANKN
	FROM (
		SELECT
			ITEM.ITEM_CODE AS [CODE]
			,ITEM.ITEM_NAME AS [NAME]
			,SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS [SAL_AMT]
			,[ALL_SUM].[ALL_SAL_AMT]
		FROM SALES
		INNER JOIN SALES_DTL
			ON SALES_DTL.SALES_NO = SALES.SALES_NO
		INNER JOIN ITEM
			ON ITEM.ITEM_CODE = SALES_DTL.ITEM_CODE
		INNER JOIN (
			SELECT
				SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS [ALL_SAL_AMT]
			FROM SALES
			INNER JOIN SALES_DTL
				ON SALES_DTL.SALES_NO = SALES.SALES_NO
			WHERE SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
		) AS [ALL_SUM]
			ON 1 = 1
		WHERE SALES.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
		GROUP BY
			ITEM.ITEM_CODE
			,ITEM.ITEM_NAME
			,[ALL_SUM].[ALL_SAL_AMT]
		) AS [SAL_SUM]
) AS [SAL_SUM2]
	ON (
	    [SAL_SUM2].RANKN < [SAL_SUM].RANKN
	    OR
	    ([SAL_SUM2].RANKN = [SAL_SUM].RANKN AND [SAL_SUM2].[CODE] <> [SAL_SUM].[CODE])
	)
GROUP BY
	[SAL_SUM].[CODE]
	,[SAL_SUM].[NAME]
	,[SAL_SUM].[SAL_AMT]
	,[SAL_SUM].[ALL_SAL_AMT]
ORDER BY
	[SAL_SUM].[SAL_AMT] DESC
	,[SAL_SUM].[CODE] DESC
;