WITH TMP AS (SELECT
    I.ITEM_CODE AS ITEM_CODE
    , I.ITEM_NAME AS ITEM_NAME
    , IFNULL(MAX(SO.ORDER_DATE), '1900-01-01') AS ORDER_DATE
    , SUM(IFNULL(SO.ORDER_QTY, 0)) AS ORDER_QTY
FROM ITEM AS I
LEFT JOIN (
    SELECT
        D.ITEM_CODE AS ITEM_CODE
        , D.ORDER_QTY AS ORDER_QTY
        , O.ORDER_DATE AS ORDER_DATE
    FROM ORDERS_DTL D
    INNER JOIN ORDERS O
    ON D.ORDER_NO = O.ORDER_NO
    AND O.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
) SO
ON I.ITEM_CODE = SO.ITEM_CODE
GROUP BY I.ITEM_CODE, I.ITEM_NAME
), TMP2 AS (
SELECT
    CASE
        WHEN TMP.ORDER_QTY = 0 THEN 0
        ELSE RANK() OVER(ORDER BY ORDER_QTY DESC, ORDER_DATE DESC, ITEM_CODE DESC)
    END AS ITEM_POPULAR_RANK
    , TMP.*
FROM TMP
ORDER BY ORDER_QTY DESC, ORDER_DATE DESC, ITEM_CODE DESC
)
UPDATE ITEM
SET ITEM_POPULAR_RANK = TMP2.ITEM_POPULAR_RANK
FROM TMP2
WHERE ITEM.ITEM_CODE = TMP2.ITEM_CODE;