update item
set item_popular_rank=0;

with sum_qty_wk as (
select 
    odd.item_code
    , sum(odd.order_qty) as sum_qty
    , max(od.order_date) as max_date
from orders as od
inner join orders_dtl as odd on odd.order_no=od.order_no
where od.order_date between '2023-04-01' and '2023-06-30'
group by odd.item_code
),
rank_item_wk as (
    select
        item_code
        , row_number() over (order by sum_qty desc, max_date desc, item_code desc) as popular_rank
    from sum_qty_wk
)
update item
set item_popular_rank=riw.popular_rank
from (select item_code, popular_rank from rank_item_wk) as riw
where item.item_code=riw.item_code;