update ITEM
set RECOMMEND_ITEM_RANK = (
select 
 case when O.ORDER_QTY is null then 0
       else rank() over(order by O.ORDER_QTY desc, O.ORDER_DATE desc, I.ITEM_CODE desc)
  end
from ITEM I
     left join (
     select D.ITEM_CODE
     , sum(D.ORDER_QTY) ORDER_QTY
     , MAX(O.ORDER_DATE) ORDER_DATE
     from ORDERS_DTL D
     inner join ORDERS O
       on D.ORDER_NO = O.ORDER_NO
       and O.ORDER_DATE >= '2023-04-01'
       and  O.ORDER_DATE <= '2023-06-30'
     where D.ORDER_QTY > 0
     group by D.ITEM_CODE
     ) O
     on O.ITEM_CODE = I.ITEM_CODE
where I.ITEM_CODE = ITEM.ITEM_CODE
group by
 I.ITEM_CODE
)