update ITEM 
set ITEM_POPULAR_RANK = 0;


with tbl as (
select ITEM_CODE, rank()over(order by ORDER_QTY desc, ORDER_DATE desc, ITEM_CODE desc) as ranked
froM(
select orders_dtl.ITEM_CODE,
sum(ORDER_QTY) as ORDER_QTY,
max(order_date) as ORDER_DATE
from orders o
join orders_dtl on o.order_no = orders_dtl.order_no
where order_date between '2023-04-01' and '2023-06-01'
group by orders_dtl.ITEM_CODE
)
)
update ITEM 
set ITEM_POPULAR_RANK = tbl.ranked
from tbl 
where ITEM.ITEM_CODE = tbl.ITEM_CODE