WITH SUB1 AS
(SELECT OD.ITEM_CODE AS ITEM_CODE,
SUM(ORDER_QTY) AS ORDER_AMT,
MAX(ORDER_DATE) AS LAST_ORDER_DATE
FROM ORDERS_DTL OD INNER JOIN ORDERS O
ON OD.ORDER_NO = O.ORDER_NO
AND ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY ITEM_CODE),
SUB2 AS
(SELECT ITEM_CODE FROM ITEM)
UPDATE ITEM
SET ITEM_POPULAR_RANK = CASE
WHEN SUB3.ORDER_AMT IS NOT NULL THEN RANKING
ELSE 0 END
FROM(SELECT SUB2.ITEM_CODE AS ITEM_CODE, ORDER_AMT,
RANK() OVER (ORDER BY ORDER_AMT DESC, LAST_ORDER_DATE DESC, SUB2.ITEM_CODE DESC) AS RANKING
FROM SUB2 LEFT JOIN SUB1
ON SUB2.ITEM_CODE = SUB1.ITEM_CODE) AS SUB3
WHERE ITEM.ITEM_CODE = SUB3.ITEM_CODE