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