with sm as (select 
ITEM_CODE
, SUM(ORDER_QTY) as SQ
, MAX(ORDER_DATE) as MO
from 
ORDERS join ORDERS_DTL on ORDERS.ORDER_NO = ORDERS_DTL.ORDER_NO
where ORDER_DATE between "2023-04-01" and "2023-06-30"
group by ITEM_CODE
)
, smr as (
select ITEM_CODE, rank() over (order by SQ, MO, ITEM_CODE desc) as rk from sm
)
, nsmr as (
select ITEM_CODE, 0 as rk
from ITEM
where NOT ITEM_CODE IN (
select ITEM_CODE from sm
)
)
, ssmr as (
select * from smr union all
select * from nsmr
)
update item set ITEM_POPULAR_RANK = (select ssmr.rk from ssmr where item.item_code = ssmr.ITEM_CODE)