update item 
set item_popular_rank = 0 
;

with tmp as 
(
select 
  d.item_code 
  ,sum(d.order_qty) as q 
  ,max(o.order_date) as latest_date 
from orders as o 
inner join orders_dtl as d 
  on o.order_no = d.order_no 
where o.order_date >= '2023-04-01'
  and o.order_date < '2023-07-01'
group by 1
)
with r as 
(
select 
  item_code 
  ,rank() over(order by q desc, latest_date desc, item_code desc) as rank 
from tmp 
)


update item 
set item_popular_rank = (select r.rank from rank where item.item_code = r.item_code)
;