with IPR as(
select 
 I.ITEM_CODE
, 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 ITEM_POPULAR_RANK
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
group by
 I.ITEM_CODE
)
update ITEM
set ITEM_POPULAR_RANK = IPR.ITEM_POPULAR_RANK
from IPR
where IPR.ITEM_CODE
;