WITH
    RankedItems AS (
        SELECT
            OD.ITEM_CODE
          , SUM(OD.ORDER_QTY) AS TotalQty
          , MAX(O.ORDER_DATE) AS LatestOrderDate
          , RANK() OVER (
                ORDER BY
                    SUM(OD.ORDER_QTY) DESC
                  , MAX(O.ORDER_DATE) DESC
                  , OD.ITEM_CODE DESC
            ) AS PopularityRank
        FROM
            ITEM I
            LEFT JOIN ORDERS_DTL OD ON I.ITEM_CODE = OD.ITEM_CODE
            LEFT JOIN ORDERS O ON OD.ORDER_NO = O.ORDER_NO
        WHERE
            O.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
            OR O.ORDER_DATE IS NULL
        GROUP BY
            OD.ITEM_CODE
    )
UPDATE ITEM
SET
    ITEM_POPULAR_RANK = COALESCE(
        (
            SELECT
                PopularityRank
            FROM
                RankedItems
            WHERE
                ITEM.ITEM_CODE = RankedItems.ITEM_CODE
        )
      , 0
    );