WITH A AS(
	SELECT
		ITEM_CODE
		,CASE SUM_QTY
			WHEN 0 THEN 0
			ELSE NUM
		END AS NUM
	FROM(
		SELECT
			ITEM_CODE
			,SUM_QTY,
			MAX_ORDER_DATE
			,ROW_NUMBER() OVER(ORDER BY SUM_QTY DESC, MAX_ORDER_DATE DESC, ITEM_CODE DESC) NUM
		FROM
		(
			SELECT 
				I.ITEM_CODE
				,SUM(
					CASE WHEN ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30' THEN ORDER_QTY
						 ELSE 0
					END) AS SUM_QTY
				,MAX(ORDER_DATE) MAX_ORDER_DATE
			FROM
				ITEM I
				LEFT OUTER JOIN ORDERS_DTL OD
					ON OD.ITEM_CODE = I.ITEM_CODE
				LEFT OUTER JOIN (SELECT * FROM ORDERS WHERE ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30') O
					ON O.ORDER_NO = OD.ORDER_NO
				GROUP BY I.ITEM_CODE
		)
	)
)
UPDATE
	ITEM
SET
	ITEM_POPULAR_RANK = (SELECT NUM FROM A WHERE ITEM.ITEM_CODE = A.ITEM_CODE)
;