with tmp as(
select
      ord.item_code
      ,SUM(ord.ORDER_QTY) as qty
      ,MAX(ordd.ORDER_DATE) as day
from 
    ORDERS_DTL as ord
inner join orders as ordd
    on ord.ORDER_NO = ordd.ORDER_NO
where
    ordd.ORDER_DATE BETWEEN date('2023-04-01') AND date('2023-06-30')
group by
    ord.item_code

),tmp2 as(
select
    item_code
    ,ROW_NUMBER() OVER (ORDER BY qty DESC, day DESC, item_code DESC) AS rank
from
    tmp
)

UPDATE ITEM
SET ITEM_POPULAR_RANK = COALESCE((
    SELECT rank
    FROM tmp2
    WHERE ITEM.ITEM_CODE = tmp2.item_code
), 0);