update ITEM 
set ITEM_POPULAR_RANK = B.ida
from
(
select row_number() over (order by cnt desc, max(ORDER_DATE) desc, it.ITEM_CODE desc) as ida
, it.ITEM_CODE , ifnull(cnt, 0) as cnt2, 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
   inner join (
   select it.ITEM_CODE , 
    sum(ORDER_QTY) 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

   where o.ORDER_DATE between '2023-04-01' and '2023-06-30'
   group by it.ITEM_CODE
   order by cnt desc,dt desc, it.ITEM_CODE desc) as B
   where ITEM.ITEM_CODE = B.ITEM_CODE
   ;


update ITEM 
set ITEM_POPULAR_RANK = 0
from
(
select it.ITEM_CODE , 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
   left join (
   select it.ITEM_CODE , 
   sum(ORDER_QTY) 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
   ) as B
   where ITEM.ITEM_CODE = B.ITEM_CODE and B.cnt is null;