WITH OrderSummary AS (
    SELECT
        od.ITEM_CODE,
        SUM(od.ORDER_QTY) as TOTAL_QTY,
        MAX(o.ORDER_DATE) as LATEST_ORDER_DATE
    FROM ORDERS o
    JOIN ORDERS_DTL od ON o.ORDER_NO = od.ORDER_NO
    WHERE o.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY od.ITEM_CODE
)

, RankedOrders AS (
    SELECT
        os.ITEM_CODE,
        os.TOTAL_QTY,
        os.LATEST_ORDER_DATE,
        RANK() OVER (ORDER BY os.TOTAL_QTY DESC, os.LATEST_ORDER_DATE DESC, os.ITEM_CODE DESC) AS POPULAR_RANK
    FROM OrderSummary os
)

UPDATE ITEM
SET ITEM_POPULAR_RANK = COALESCE(ro.POPULAR_RANK, 0)
FROM RankedOrders ro
WHERE ITEM.ITEM_CODE = ro.ITEM_CODE;