update
    ITEM
set
    ITEM_POPULAR_RANK = 0;
with BASE as (
select
    ITEM_CODE,
    sum(ORDER_QTY) as item_qty,
    max(ORDER_DATE) as new_order_date
from
    ORDERS o
inner join
    ORDERS_DTL od
on
    od.ORDER_NO = o.ORDER_NO
where
    ORDER_DATE between '2023-04-01' and '2023-06-30'
group by
    ITEM_CODE
),
C00 as (
select 
    ITEM_CODE,
    rank() over(order by item_qty desc, new_order_date desc, ITEM_CODE desc) as rank
from
    BASE
)
update
    ITEM
set
    ITEM_POPULAR_RANK = C00.rank
from
    C00
where
    C00.ITEM_CODE = ITEM.ITEM_CODE;