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

Ranks AS (
    SELECT
        ITEM_CODE,
        TOTAL_QTY,
        LATEST_ORDER_DATE,
        (SELECT COUNT(*) + 1 
         FROM OrderQty as oq2 
         WHERE oq2.TOTAL_QTY > oq1.TOTAL_QTY 
               OR (oq2.TOTAL_QTY = oq1.TOTAL_QTY AND oq2.LATEST_ORDER_DATE > oq1.LATEST_ORDER_DATE)
               OR (oq2.TOTAL_QTY = oq1.TOTAL_QTY AND oq2.LATEST_ORDER_DATE = oq1.LATEST_ORDER_DATE AND oq2.ITEM_CODE > oq1.ITEM_CODE)
        ) as RANK
    FROM
        OrderQty oq1
)

UPDATE ITEM
SET ITEM_POPULAR_RANK = 
    (SELECT IFNULL(MAX(RANK), 0)
     FROM Ranks
     WHERE Ranks.ITEM_CODE = ITEM.ITEM_CODE);