update item set item_popular_rank = 0 ;

with sum_qtw as (
    select ODT.item_code
        , sum(ODT.order_qty) as sum_qty
        , max(OD.order_date) as max_date
    from orders_dtl as ODT
        inner join orders as OD on ODT.order_no = OD.order_no
    where OD.order_date between '2023-04-01' and '2023-06-30'
    group by ODT.item_code
)
, rank_item as (
    select item_code
        , ROW_NUMBER() over ( order by sum_qty desc , max_date desc , item_code desc) as item_rank
    from sum_qtw
)

update item
    set item_popular_rank = RANK.item_rank
    from rank_item as RANK
    where item.item_code = RANK.item_code