update item set item_popular_rank = 0;
with s as (select * from orders where order_date between '2023-04-01' and '2023-06-30')
, sg as (
select 
od.ITEM_CODE as ITEM_CODE
, sum(od.ORDER_QTY) as TOTAL
, max(s.ORDER_DATE) as LAST_ORDER_DATE
from s join orders_dtl od on s.ORDER_NO = od.ORDER_NO
group by od.ITEM_CODE
)
, sgr as (
select *, rank() over (order by TOTAL desc, LAST_ORDER_DATE desc, ITEM_CODE desc) as ra from sg
)
update item set item_popular_rank = (
  select sgr.ra from sgr where item.ITEM_CODE = sgr.ITEM_CODE
) where item_code in (select item_code from sgr)