WITH aggregated_orders AS (

  SELECT

    od.ITEM_CODE,

    SUM(od.ORDER_QTY) AS TOTAL_ORDER_QTY,

    MAX(o.ORDER_DATE) AS LATEST_ORDER_DATE

  FROM

    ORDERS o

    JOIN ORDERS_DTL od ON o.ORDER_NO = od.ORDER_NO

  WHERE

    o.ORDER_DATE >= '2023-04-01' AND o.ORDER_DATE <= '2023-06-30'

  GROUP BY

    od.ITEM_CODE

),

ranked_items AS (

  SELECT

    a.ITEM_CODE,

    a.TOTAL_ORDER_QTY,

    a.LATEST_ORDER_DATE,

    ROW_NUMBER() OVER (

      ORDER BY

        a.TOTAL_ORDER_QTY DESC,

        a.LATEST_ORDER_DATE DESC,

        a.ITEM_CODE DESC

    ) AS ITEM_POPULAR_RANK

  FROM

    aggregated_orders a

  JOIN ITEM i ON a.ITEM_CODE = i.ITEM_CODE

)

UPDATE ITEM

SET ITEM_POPULAR_RANK = COALESCE(

  (SELECT r.ITEM_POPULAR_RANK FROM ranked_items r WHERE ITEM.ITEM_CODE = r.ITEM_CODE),

  0

)