update item set ITEM_POPULAR_RANK=0;
with t1 as (select
    D.ITEM_CODE,
    max(ORDER_DATE) DT,
   SUM(ORDER_QTY) qty
from ORDERS O  join ORDERS_DTL D on D.ORDER_NO=O.ORDER_NO
where O.ORDER_DATE between '2023-04-01' and '2023-06-30'
group by D.ITEM_CODE)
,t2 as (select
            t1.*,
            rank() over(order by qty desc, dt desc,ITEM_CODE
                ) rnk
            from t1)
update item set ITEM_POPULAR_RANK = (select t2.rnk from t2 where t2.ITEM_CODE=item.ITEM_CODE)
where item.ITEM_CODE in (select t2.ITEM_CODE from t2);