with tmp as(
select a.item_code,
       max(b.order_date) as max_order_date,
       sum(a.ORDER_QTY) as sum_order_qty
  from ORDERS_DTL a
  join ORDERS b
    on a.order_no = b.order_no
 where b.ORDER_DATE between '2023-04-01' and '2023-06-30'
group by a.item_code),
tmp2 as(
select a.item_code,
case when b.item_code is null then 0
     else Row_Number() over(order by sum_order_qty desc,max_order_date desc,a.item_code)
     end as rn
  from ITEM a
  left join tmp b
    on a.item_code = b.item_code)
update ITEM
  set ITEM_POPULAR_RANK=(select rn
                           from tmp2
                          where  tmp2.item_code = ITEM.item_code)