with item_info as (
    select
        orders_dtl.item_code,
        sum(orders_dtl.order_qty) as total_qty,
        max(orders.order_date) as last_order_date
    from
        orders
        inner join orders_dtl on orders.order_no = orders_dtl.order_no
            
    where
        orders.order_date >= '2023-04-01' and
        orders.order_date <= '2023-06-30'
    group by
        orders_dtl.item_code
),
item_ranks as (
    select
        item_code,
        rank() over (order by total_qty desc, last_order_date desc, item_code desc) as item_rank
    from
        item_info
    order by
        item_rank desc
)
update item
set item_popular_rank =
    coalesce(
        (select item_rank from item_ranks where item_ranks.item_code = item.item_code),
        0
    )
;