update ITEM 
set ITEM_POPULAR_RANK = B.ida
from
(
select row_number() over (order by ifnull(cnt, 0) desc, max(ORDER_DATE) desc, it.ITEM_CODE desc) as ida
, it.ITEM_CODE , ifnull(cnt, 0), max(ORDER_DATE) as dt from ITEM as it
   left join ORDERS_DTL as od on od.ITEM_CODE  = it.ITEM_CODE 
   left join ORDERS as o on o.ORDER_NO = od.ORDER_NO
   left join (
   select it.ITEM_CODE , ifnull(sum(ORDER_QTY),0) as cnt from ITEM as it
   left join ORDERS_DTL as od on od.ITEM_CODE  = it.ITEM_CODE 
   left join ORDERS as o on o.ORDER_NO = od.ORDER_NO
   where ORDER_DATE between '2023-04-01' and '2023-06-30'

   group by it.ITEM_CODE
   order by cnt desc
) as temp on temp.ITEM_CODE = it.ITEM_CODE

   group by it.ITEM_CODE
   order by cnt desc,dt desc, it.ITEM_CODE desc) as B
   where ITEM.ITEM_CODE = B.ITEM_CODE