-- update item set item_popular_rank=0;
with t as (
select item_code, rank() over(order by sum(order_qty) desc, max(order_date) asc, max(item_code) desc) as r
from orders as x inner join
orders_dtl as y on x.order_no=y.order_no
where order_date>="2023-04-01"and order_date<="2023-06-30"
group by 1
)
update item set item_popular_rank=coalesce((select r from t where t.item_code=item.item_code), 0)