update item
set
  ITEM_POPULAR_RANK = ifnull((
    select
      rnk
    from
      (
        with
          a as (
            select
              sum(ORDER_QTY) qty,
              max(ord.ORDER_DATE) dt,
              dtl.ITEM_CODE
            from
              ORDERS ord
              join ORDERS_DTL dtl on ord.ORDER_NO = dtl.ORDER_NO
              join item on dtl.ITEM_CODE = item.ITEM_CODE
            where
              ord.ORDER_DATE between '2023-04-01' and '2023-06-99'
            group by
              dtl.ITEM_CODE
          )
        select
          item_code,
          rank() over (
            order by
              qty desc,
              dt desc,
              ITEM_CODE desc
          ) rnk
        from
          a
      ) a
    where
      a.item_code = item.item_code
  ),0)