WITH QTY_INFO AS (
    SELECT
        MAX(ORDER_DATE) AS LATEST_DAY
        , ORD_D.ITEM_CODE AS CODE
        , SUM(ORD_D.ORDER_QTY) AS QTY
    FROM
        ORDERS AS ORD
    INNER JOIN
        ORDERS_DTL AS ORD_D
    ON
        ORD.ORDER_NO = ORD_D.ORDER_NO
    WHERE
        ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY
        ORD_D.ITEM_CODE
)
, RANKING_SUB AS (
    SELECT
        CODE
        , RANK() OVER(
                ORDER BY
                    QTY DESC
                    , LATEST_DAY DESC
                    , CODE DESC
            )
            AS RANK
    FROM
        QTY_INFO
)
, RANKING_ALL AS (
    SELECT
        ITEM.ITEM_CODE AS CODE
        , IFNULL(RANK, 0) AS RANK
    FROM
        ITEM
    LEFT JOIN
        RANKING_SUB ON ITEM.ITEM_CODE = RANKING_SUB.CODE
)

UPDATE
    ITEM
SET
    ITEM_POPULAR_RANK = 
    (
        SELECT
            RANKING_ALL.RANK
        FROM
            RANKING_ALL
        WHERE
            ITEM.ITEM_CODE = RANKING_ALL.CODE
    )
;