with BASE as (
select
    ITEM_CODE,
    count(ORDER_QTY) as item_qty,
    max(ORDER_DATE) as new_order_date
from
    ORDERS o
inner join
    ORDERS_DTL od
on
    od.ORDER_NO = o.ORDER_NO
where
    ORDER_DATE between '2023-04-01' and '2023-06-30'
group by
    ITEM_CODE
),
C00 as (
select 
    *,
    rank() over(order by item_qty desc, new_order_date desc, ITEM_CODE desc) as rank
from
    BASE
),
CODE as (
select
    ITEM_CODE
from
    ITEM
group by
    ITEM_CODE
),
C01 as (
select
    CODE.ITEM_CODE,
    COALESCE(rank, 0) as rank
from
    CODE
left join
    C00
on
    C00.ITEM_CODE = CODE.ITEM_CODE
)
update
    ITEM
set
    ITEM_POPULAR_RANK = C01.rank
from
    C01
where
    C01.ITEM_CODE = ITEM.ITEM_CODE