UPDATE ITEM SET
    ITEM_POPULAR_RANK = ORDER_INFO.ITEM_POPULAR_RANK
FROM
    (
        WITH ORDER_INFO AS (
        SELECT
             ITEM_CODE
            ,ORDER_DATE
            ,SUM(ORDER_QTY) AS ORDER_QTY
        FROM (SELECT ORDER_NO, MAX(ORDER_DATE) AS ORDER_DATE FROM ORDERS WHERE ORDER_DATE BETWEEN DATE('2023-04-01') AND DATE('2023-06-30')) AS ORDERS
             INNER JOIN (SELECT ORDER_NO ,ITEM_CODE, ORDER_QTY FROM ORDERS_DTL) AS ORDERS_DTL
                 ON ORDERS.ORDER_NO = ORDERS_DTL.ORDER_NO
        )
        SELECT
             ITEM_CODE
            ,RANK() OVER(ORDER BY ITEM_CODE DESC, ORDER_DATE DESC, ITEM_CODE DESC) AS ITEM_POPULAR_RANK
        FROM ORDER_INFO
    ) AS ORDER_INFO
WHERE
   ITEM.ITEM_CODE = ORDER_INFO.ITEM_CODE
;