update ITEM set ITEM_POPULAR_RANK=0;

with base as (
select
    ITEM_CODE
    ,sum(ORDER_QTY) as AMT
    ,max(ORDER_DATE) as OD_DATE
from
    ORDERS_DTL as od
    inner join
    ORDERS as o
    on od.ORDER_NO=o.ORDER_NO
where
    ORDER_DATE between "2023-04-01" and "2023-06-30"
group by
    ITEM_CODE
having
    0<AMT
)
,pr as (
select
    ITEM_CODE
    ,ROW_NUMBER() over (order by AMT desc,OD_DATE desc,ITEM_CODE desc) as ITEM_POPULAR_RANK
from
    base
)
update 
    ITEM as i
set 
    ITEM_POPULAR_RANK = pr.ITEM_POPULAR_RANK
from
    pr
where
    i.ITEM_CODE = pr.ITEM_CODE