with item_qty as (
    SELECT
        d.item_code
        ,sum(d.order_qty) as qty
        ,max(o.order_date) as latest_date
    FROM ORDERS o
    LEFT JOIN ORDERS_DTL d
        ON o.order_no = d.order_no
    WHERE ORDER_DATE between '2023-04-01' and '2023-06-30'	
    group by d.item_code
)
,item_rank as (
    SELECT 
    *,row_number() over (order by qty DESC, latest_date DESC, item_code DESC) as ITEM_POPULAR_RANK
    FROM item_qty
)
,item_rank_edi as (
    SELECT
        item.item_code
        ,ifnull(item_rank.ITEM_POPULAR_RANK,0)
    FROM item
    LEFT JOIN item_rank
        ON item.item_code = item_rank.item_code
)

UPDATE item SET ITEM_POPULAR_RANK = (
select ITEM_POPULAR_RANK FROM item_rank_edi
where item_rank_edi.ITEM_code = item.item_code
)