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
)
,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 =
case 
when r.rank is null then 0
else r.rank
end from r where item.item_code = r.item_code
;