WITH tmp0 AS (
    SELECT ITEM_CODE, rank() OVER (ORDER BY SUM(ORDER_QTY) DESC, MAX(ORDER_DATE) DESC, ITEM_CODE DESC) ITEM_RANK
    FROM ORDERS_DTL
    LEFT JOIN ORDERS ON ORDERS.ORDER_NO = ORDERS_DTL.ORDER_NO
    WHERE ORDERS.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY ITEM_CODE
),
tmp1 AS (
    SELECT ITEM.ITEM_CODE,
        CASE WHEN tmp0.ITEM_RANK IS NULL THEN 0 ELSE tmp0.ITEM_RANK END ITEM_RANK
    FROM ITEM
    LEFT JOIN tmp0 ON ITEM.ITEM_CODE = tmp0.ITEM_CODE
)
UPDATE ITEM
SET ITEM_POPULAR_RANK = tmp1.ITEM_RANK
FROM tmp1
WHERE ITEM.ITEM_CODE = tmp1.ITEM_CODE