with smr as (select 
ITEM_CODE
, SUM(ORDER_QTY) as SQ
, MAX(ORDER_DATE) as MO
, rank() over (order by SUM(ORDER_QTY)desc, MAX(ORDER_DATE) desc, ITEM_CODE desc) as rk
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 desc, MO desc, ITEM_CODE desc) as rk from sm
-- )
, nsmr as (
select ITEM_CODE, 0 as SQ, 0 as MO, 0 as rk
from ITEM
where NOT ITEM_CODE IN (
select ITEM_CODE from smr
)
)
, 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)